converting rows to columns

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
PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZHere'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.Pieces), 0) AS PopCorn,
ISNULL(SUM(c.Pieces), 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" <poojaahirrao@.hotmail.com> wrote in message
news:94541780.0401100526.5d3178ae@.posting.google.c om...
> 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
PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ|||Hi

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

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

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

John

"Pooj" <poojaahirrao@.hotmail.com> wrote in message
news:94541780.0401100526.5d3178ae@.posting.google.c om...
> 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
PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<btp2ft$h0i$1@.titan.btinternet.com>...
> Hi
> Check out a crosstab query that will transform your rows to columns:
> http://support.microsoft.com/defaul...b;EN-US;q175574
> There are many posts about pivot tables or crosstab queries.. Search google
> for more.
> John
> "Pooj" <poojaahirrao@.hotmail.com> wrote in message
> news:94541780.0401100526.5d3178ae@.posting.google.c om...
> > 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
> PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
> ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ

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|||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" <danguzman@.nospam-earthlink.net> wrote in message news:<8HTLb.1542$i4.11@.newsread1.news.atl.earthlink.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.Pieces), 0) AS PopCorn,
> ISNULL(SUM(c.Pieces), 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" <poojaahirrao@.hotmail.com> wrote in message
> news:94541780.0401100526.5d3178ae@.posting.google.c om...
> > 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
> PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
> ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ|||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,Cheese etc . Basically
these records can vary and those should be converted as columns at
runtime...

Please help...

Thanks,
Pooj.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<btp2ft$h0i$1@.titan.btinternet.com>...
> Hi
> Check out a crosstab query that will transform your rows to columns:
> http://support.microsoft.com/defaul...b;EN-US;q175574
> There are many posts about pivot tables or crosstab queries.. Search google
> for more.
> John
> "Pooj" <poojaahirrao@.hotmail.com> wrote in message
> news:94541780.0401100526.5d3178ae@.posting.google.c om...
> > 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
> PLZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZZZZZZZZZZZZ
> ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ ZZZZZZ|||Hi

This may help...

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

John|||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