SQLTeam.com | Weblogs | Forums

Converting rows into column


#1

Hi There,
I would like to be able to convert rows into columns. I have a SQL statement whose output is like:

Id Supplier
1 1
2 2
2 3
3 a
3 b
3 c

I would like the output to look like:

1 1
2 2 3
3 a b c

Can anyone help please?


#2

try this::

declare @temp table (id int , Supplier varchar(5) )
Insert into @temp values (1,'1'),(2,'2'),(2,'3'),(3,'a'),(3,'b'),(3,'c')

Select * from (
Select id
,supplier
,ROW_number()over(partition by id order by id) as rw
from @temp
)a
Pivot(
Min(Supplier)
for rw in ([1],[2],[3])
) as Pvttable


#3

That solves the very specific problem but what happens when someone adds, say, 10 new suppliers to ID #1? :wink:


#4

Is there a guaranteed maximum number of Suppliers that a given ID can ever have? And do you want a column for each supplier that an ID might have. For example, would the line for ID #3 have the supplier ids as a single column with all of them separated by spaces or would there be a total of 3 supplier columns?

It would also help if we knew why you wanted to denormalize the data this way.