group data into multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

group data into multiple columns

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2010-02-09 : 06:08:47

Hello,

I have a table with 2 columns (see #table1) and i would like to unpack the data so that there is 1 row per LocID and as many column headings as required. (These don’t have a limit)

I have provided sample SQL of #table1 source data and the table i would like to create at the end of the SQL.

Can anyone help?
Thanks

--sample SQL
create table
#table1
(
LocID nvarchar (30),
Region nvarchar (30)
)

insert into #table1
select '40331606', 'Area1' UNION all
select '40331606', 'Area1' UNION all
select '40331606', 'Area2' UNION all
select '40331607', 'Area7' UNION all
select '40331608', 'Area9'

select * from #table1

create table
#tableIwouldlike
(
LocID nvarchar (30),
Region1 nvarchar (30),
Region2 nvarchar (30),
Region3 nvarchar (30)

)

insert into #tableIwouldlike
select '40331606', 'Area1', 'Area1', 'Area2' UNION all
select '40331607', 'Area7', null, null UNION all
select '40331608', 'Area9', null, null

select * from #tableIwouldlike

drop table #table1
drop table #tableIwouldlike

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 07:33:03

[code]select IDENTITY(int,1,1) AS ID,* into #res from #table1

SELECT LocID,
MAX(CASE WHEN Seq=1 THEN Region ELSE NULL END) AS Region1,
MAX(CASE WHEN Seq=2 THEN Region ELSE NULL END) AS Region2,
MAX(CASE WHEN Seq=3 THEN Region ELSE NULL END) AS Region3
FROM(
SELECT (SELECT COUNT(*) FROM #res where LocID=r.LocID AND ID < r.ID) +1 AS Seq,
*
FROM #res r
)t
GROUP BY LocID
[/code]

DLTaylor
Posting Yak Master

136 Posts

Posted - 2010-02-09 : 08:26:46

Genius are the words id use!

Thanks for you help Visakh – works perfectly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-09 : 08:33:22

welcome

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-11 : 07:30:09

[code]DECLARE @res TABLE (ID INT IDENTITY(1,1),
LocID nvarchar (30),
Region nvarchar (30));

INSERT INTO @res SELECT * FROM #table1

SELECT LocID,
MAX(CASE WHEN Seq=1 THEN Region ELSE NULL END) AS Region1,
MAX(CASE WHEN Seq=2 THEN Region ELSE NULL END) AS Region2,
MAX(CASE WHEN Seq=3 THEN Region ELSE NULL END) AS Region3
FROM (SELECT (SELECT COUNT(*)
FROM @res
WHERE LocID=r.LocID
AND ID < r.ID) +1 AS Seq,
*
FROM @res r)t
GROUP BY LocID

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-11 : 07:44:25

quote:

Originally posted by ms65g

DECLARE @res TABLE (ID INT IDENTITY(1,1),
                    LocID nvarchar (30),
                    Region nvarchar (30));
                    
INSERT INTO @res SELECT * FROM #table1

SELECT LocID,
       MAX(CASE WHEN Seq=1 THEN Region ELSE NULL END) AS Region1,
       MAX(CASE WHEN Seq=2 THEN Region ELSE NULL END) AS Region2,
       MAX(CASE WHEN Seq=3 THEN Region ELSE NULL END) AS Region3
  FROM (SELECT  (SELECT COUNT(*) 
                   FROM @res 
                  WHERE LocID=r.LocID 
                    AND ID < r.ID) +1 AS Seq,
               *
         FROM @res r)t
 GROUP BY LocID

Isnt this same as what I posted?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-11 : 08:01:48

--> Isnt this same as what I posted? <--

You used temporary table and IDENTITY INTO function instead of I used IDENTITY property and variable table!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources