rows to columns - 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 2005 Forums

Transact-SQL (2005)

rows to columns

Author

Topic

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-06-21 : 15:54:13

I have a table that stores cross reference numbers.
The table has two columns: number and xref_number.
There are multiple entries with the same value in the numbers column and up to 5 different values in the xref column.

S123 - 343M
S123 - 455M
....

I would like to create a table that uses this data but has only one entry for number and 5 columns that store the respective xref numbers.

S123 - 343M - 455M
...

Any help would be appreciated.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-21 : 16:10:45

Look up PIVOT operator.

You will need something like this

declare @t table (number varchar(10), xref_number varchar(10))
insert @t
select 'S123','343M' union all
select 'S123','455M' union all
select 'S123','501M' union all
select 'S123','615M' union all
select 'S123','999M' union all
select 'S234','343M' union all
select 'S234','455M' union all
select 'S234','501M' union all
--select 'S234','615M' union all
select 'S234','999M'


SELECT number, [343M] AS Xref1, [455M] AS Xref2, [501M] AS Xref3, [615M] AS Xref4, [999M] AS Xref5
FROM 
(SELECT * from @t ) p
PIVOT
(
MAX (xref_number)
FOR xref_number IN
( [343M], [455M], [501M], [615M], [999M] )
) AS pvt
order by number

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-06-21 : 17:20:52

Thank you. I'm not quite sure I understand correctly.
My table has 400,000 records.
Do I have to manually write down all the values in the select union all statement?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-22 : 01:24:51

Vijay has used "Select" and union all for explanation.

Your select query will be as follows : (Its from Vijay's post only)

SELECT number, [343M] AS Xref1, [455M] AS Xref2, [501M] AS Xref3, [615M] AS Xref4, [999M] AS Xref5
FROM
(SELECT * from @t ) p
PIVOT
(
MAX (xref_number)
FOR xref_number IN
( [343M], [455M], [501M], [615M], [999M] )
) AS pvt
order by number

Also have a look at link:
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-06-22 : 07:24:36

You may get better insight through the link http://www.sql-programmers.com/Blog/tabid/153/EntryId/6/Using-PIVOT-and-UNPIVOT.aspx

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources