converting rows to columns

**Dan Guzman** · Jul 20 '05, 02:33 AM

Re: converting rows to columns

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" <poojaahirrao@h otmail.com> wrote in message
news:94541780.0 401100526.5d317 8ae@posting.goo gle.com...[color=blue]
> 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
>[/color]
PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ

**John Bell** · Jul 20 '05, 02:33 AM

Re: converting rows to columns

Hi

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

Error - Office.com

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574

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

John

"Pooj" <poojaahirrao@h otmail.com> wrote in message
news:94541780.0 401100526.5d317 8ae@posting.goo gle.com...[color=blue]
> 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
>[/color]
PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ

**Mystery Man** · Jul 20 '05, 02:33 AM

Re: converting rows to columns

"John Bell" <jbellnewsposts @hotmail.com> wrote in message news:<btp2ft$h0 i$1@titan.btint ernet.com>...[color=blue]
> 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" <poojaahirrao@h otmail.com> wrote in message
> news:94541780.0 401100526.5d317 8ae@posting.goo gle.com...[color=green]
> > 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
> >[/color]
> PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
> ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ[/color]

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** · Jul 20 '05, 02:33 AM

Re: converting rows to columns

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@nosp am-earthlink.net> wrote in message news:<8HTLb.154 2$i4.11@newsrea d1.news.atl.ear thlink.net>...[color=blue]
> 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" <poojaahirrao@h otmail.com> wrote in message
> news:94541780.0 401100526.5d317 8ae@posting.goo gle.com...[color=green]
> > 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
> >[/color]
> PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
> ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ[/color]

**Pooj** · Jul 20 '05, 02:33 AM

Re: converting rows to columns

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" <jbellnewsposts @hotmail.com> wrote in message news:<btp2ft$h0 i$1@titan.btint ernet.com>...[color=blue]
> 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" <poojaahirrao@h otmail.com> wrote in message
> news:94541780.0 401100526.5d317 8ae@posting.goo gle.com...[color=green]
> > 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
> >[/color]
> PLZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ Z
> ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZZZZZ ZZZZZZZZZZZ[/color]

**John Bell** · Jul 20 '05, 02:33 AM

Re: converting rows to columns

Hi

This may help...

Dynamic Cross-Tabs/Pivot Tables - SQLTeam.com

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

John

**Steve Dassin** · Jul 20 '05, 02:36 AM

Re: converting rows to columns

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 @

https://www.rac4sql.net

converting rows to columns

converting rows to columns

Comment

Comment

Comment

Comment

Comment

Comment

Comment