CODEWITHSUNDEEP
sqlsql-server-2012
StackUser
StackUser

Reputation: 5398

Convert Rows to Column in SQL Server using Pivot

I am new to pivot, I have a table like the below. It has Model Number and Group No details. The table contains 10 distinct groups.

enter image description here

I am trying to achieve the result set like this. Just want to list out all the Model under each group.

enter image description here

I tried with this SQL Query, but here I am partially getting the result. It display only one row.

SELECT *
FROM (
    SELECT DISTINCT ModelNo
        ,GroupNo
    FROM #Table
    ) d
pivot(max(ModelNo) FOR GroupNo IN (
            [1]
            ,[2]
            ,[3]
            ,[4]
            ,[5]
            ,[6]
            ,[7]
            ,[8]
            ,[9]
            ,[10]
            )) piv

I am not sure where am I missing. Kindly assist me to sort it out.

Upvotes: 1

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

I recommend conditional aggregation over pivot -- it is simply much more versatile.

What you need is row_number(), so the query looks like:

select max(case when groupno = 1 then modelno end) as group_01,
       max(case when groupno = 2 then modelno end) as group_02,
       . . .
from (select t.*,
             row_number() over (partition by groupno order by modelno) as seqnum
      from t
     ) t
group by seqnum;

You can also incorporate this idea into a pivot as well.

Upvotes: 1

Related Questions