Columns to Rows - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Columns to Rows

Author

Topic

shpinoza1980
Starting Member

17 Posts

Posted - 2011-11-10 : 05:02:01

hello,

I have this table (compLocation):
companyID int,
LocationID int,

data looks like this:

company1 1
company1 2
company1 3
company2 1
company2 2
company2 3

I need to create a new table with multiple values for the same companyID in different rows.
the new table should like this:

companyID int,
Location_1 int,
Location_2 int,
Location_3 int

and the data will be organized ass follows:

companyID Location_1 Location_2 Location_3
company1 1 2 3
company2 1 2 3

what is the query for doing that?

thanks for your help

Sql_forum
Yak Posting Veteran

50 Posts

Posted - 2011-11-10 : 05:04:18

I think UNPIVOT will do the job

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-10 : 05:07:20

quote:
I need to create a new table with multiple values for the same companyID in different rows

Why do you want to do this when you can return the result in the required format use PIVOT operator to do so ? Wouldn't you have duplicate data storing it in another table in different format ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 06:35:23

i think its for a crosstab reporting requirement. But as Tan pointed out you can very easily get data in required format from your source table itself. unless you've someother complex calculations involving individual fields, I would second Tan on this. Also keep in mind that most reporting tools have features to automatically do pivoting so if you're doing it for report, explore the options of doing this at reporting tool if possible.

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

shpinoza1980
Starting Member

17 Posts

Posted - 2011-11-10 : 07:21:10

The PIVOT option sounds good. I don't really need two tables with the same data.

Never used the PIVOT operator - what will be the right way to write it in my case/

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-10 : 09:13:26

[code]
select companyID, [1] as Location_1, [2] as Location_2, [3] as Location_3
from (
select *, loc_no = row_number() over (partition by companyID order by LocationID)
from compLocation
) d
pivot
(
max(LocationID)
for loc_no in ([1], [2], [3])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 09:42:13

if you've pivot multiple field values you can use this too


SELECT companyID,
max(case when loc_no =1 then LocationID else null end) as [loc1id],
max(case when loc_no =2 then LocationID else null end) as [loc2id],
max(case when loc_no =3 then LocationID else null end) as [loc3id],
max(case when loc_no =1 then someotherfield else null end) as [someotherfield1],
max(case when loc_no =2 then someotherfield  else null end) as [someotherfield2],
max(case when loc_no =3 then someotherfield else null end) as [someotherfield3],
....
FROM compLocation
GROUP BY companyID

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

shpinoza1980
Starting Member

17 Posts

Posted - 2011-11-13 : 04:51:57

Thanks!

since I'm building my query dynamically in c# visakh16's query works best for me.

thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-13 : 12:01:04

quote:
Originally posted by shpinoza1980

Thanks!

since I'm building my query dynamically in c# visakh16's query works best for me.

thanks a lot

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources