converting rows to columns

[Pooj]

have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out
PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZ

[Dan Guzman]

Here's one solution:

CREATE TABLE MyTable
(
begin_time smalldatetime NOT NULL,
end_time smalldatetime NOT NULL,
Name varchar(10) NOT NULL,
Pieces int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
(
begin_time,
end_time,
Name
)
)

INSERT INTO MyTable
SELECT '10:00', '10:15', 'PopCorn', 3
UNION ALL SELECT '10:15', '10:30', 'Biscuits', 5
UNION ALL SELECT '10:30', '10:45', 'PopCorn', 2

SELECT
CONVERT(char(5) , a.begin_time, 108) AS begin_time,
CONVERT(char(5) , a.end_time, 108) AS end_time,
ISNULL(SUM(b.Pi eces), 0) AS PopCorn,
ISNULL(SUM(c.Pi eces), 0) AS Biscuits
FROM
(
SELECT DISTINCT
begin_time,
end_time
FROM MyTable
) AS a
LEFT JOIN MyTable b ON
b.begin_time = a.begin_time AND
b.end_time = a.end_time AND
b.Name = 'PopCorn'
LEFT JOIN MyTable c ON
c.begin_time = a.begin_time AND
c.end_time = a.end_time AND
c.Name = 'Biscuits'
GROUP BY
a.begin_time,
a.end_time
ORDER BY
a.begin_time
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Pooj" <po**********@h otmail.com> wrote in message
news:94******** *************** ***@posting.goo gle.com...

have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ

[John Bell]

Hi

Check out a crosstab query that will transform your rows to columns:

http://support.microsoft.com/default...;EN-US;q175574

There are many posts about pivot tables or crosstab queries.. Search google
for more.

John

"Pooj" <po**********@h otmail.com> wrote in message
news:94******** *************** ***@posting.goo gle.com...

have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ

[Mystery Man]

"John Bell" <jb************ @hotmail.com> wrote in message news:<bt******* ***@titan.btint ernet.com>...

Hi

Check out a crosstab query that will transform your rows to columns:

http://support.microsoft.com/default...;EN-US;q175574

There are many posts about pivot tables or crosstab queries.. Search google
for more.

John

"Pooj" <po**********@h otmail.com> wrote in message
news:94******** *************** ***@posting.goo gle.com...

have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ

Another solution would be using a union statement

select * from departinfo where popcorn = 0
union select * from departinfo where bisuits = 0
order by begin_time

[Pooj]

Hello Dan,
Thanks for the quick response !
The solution you have provided wil not solve my problem due to
following reason :

The departinfo table records are not fixed as shown below. The time
difference can vary also the remainig data. The table will be
populated in the following format. Only the format is fixed the
records are not.

begin_datetime end_datetime Name Pieces

So my requirement is to convert the Name field into unique distinct
columns and match the pieces.
for example if the Name field contains items like Biscuits, Pizza,
PopCorn
then these many columns should be created and matched with the pieces.
Please help !
Thanks
Pooj


"Dan Guzman" <da*******@nosp am-earthlink.net> wrote in message news:<8H******* *******@newsrea d1.news.atl.ear thlink.net>...

Here's one solution:

CREATE TABLE MyTable
(
begin_time smalldatetime NOT NULL,
end_time smalldatetime NOT NULL,
Name varchar(10) NOT NULL,
Pieces int NOT NULL
CONSTRAINT PK_MyTable PRIMARY KEY
(
begin_time,
end_time,
Name
)
)

INSERT INTO MyTable
SELECT '10:00', '10:15', 'PopCorn', 3
UNION ALL SELECT '10:15', '10:30', 'Biscuits', 5
UNION ALL SELECT '10:30', '10:45', 'PopCorn', 2

SELECT
CONVERT(char(5) , a.begin_time, 108) AS begin_time,
CONVERT(char(5) , a.end_time, 108) AS end_time,
ISNULL(SUM(b.Pi eces), 0) AS PopCorn,
ISNULL(SUM(c.Pi eces), 0) AS Biscuits
FROM
(
SELECT DISTINCT
begin_time,
end_time
FROM MyTable
) AS a
LEFT JOIN MyTable b ON
b.begin_time = a.begin_time AND
b.end_time = a.end_time AND
b.Name = 'PopCorn'
LEFT JOIN MyTable c ON
c.begin_time = a.begin_time AND
c.end_time = a.end_time AND
c.Name = 'Biscuits'
GROUP BY
a.begin_time,
a.end_time
ORDER BY
a.begin_time
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Pooj" <po**********@h otmail.com> wrote in message
news:94******** *************** ***@posting.goo gle.com...

have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ

[Pooj]

Hi John,

Thanks for the quick reply.I checked the link u have provided but it
seems it
won't solve my problem b'coz DaypartInfo table records are not fixed.
They can vary E.g.my table format is as below:
begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

In this table I want to convert the Nae field records as columns i.e.
Name field may contain PoCorn ,Pizza ,Biscuits,Chees e etc . Basically
these records can vary and those should be converted as columns at
runtime...

Please help....

Thanks,
Pooj.


"John Bell" <jb************ @hotmail.com> wrote in message news:<bt******* ***@titan.btint ernet.com>...

Hi

Check out a crosstab query that will transform your rows to columns:

http://support.microsoft.com/default...;EN-US;q175574

There are many posts about pivot tables or crosstab queries.. Search google
for more.

John

"Pooj" <po**********@h otmail.com> wrote in message
news:94******** *************** ***@posting.goo gle.com...

have a urgent requirement. Please somebody help me.

I have a table departinfo with following records

begin_time end_time Name Pieces

10:00 10:15 PopCorn 3
10:15 10:30 Biscuits 5
10:30 10:45 PopCorn 2

Now I need to run a sql query and the output should be as below :

begin_time end_time PopCorn Biscuits

10:00 10:15 3 0
10:15 10:30 0 5
10:30 10:45 2 0

Please note that only one column i.e. PopCorn is created in spite of
having multiple records in the table. Similarly the records are not
fixed. I mean that
there can be n number of records and the columns should be uniquely
created.
Can somebody help me out

PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ

[John Bell]

Hi

This may help...

http://www.sqlteam.com/item.asp?ItemID=2955

John

[Steve Dassin]

You can do this very easily and without any
sql coding with the RAC utility for S2k.
It can be used to generate dynamic crosstabs
and solve all types of problems in an easy way.
It's similar in concept to Access crosstab but
much more powerful.

RAC v2.2 and QALite @
www.rac4sql.net