CODEWITHSUNDEEP
sql
Programmer
Programmer

Reputation: 129

How do i convert rows into columns?

i know this can be done with pivot but dont know how to put this in query..heres my table..

Id     Date      Code
1    1-2-2011    Code1
2    2-2-2011    Code2

Desired table:

Id 1-2-2011  2-2-2011 
1   Code1     Null
2    Null     Code2

Heres something i am trying but i want to know if there is any different way..

 SELECT [Id], '1-2-2011','2-2-2011'
 FROM ( SELECT [Id]
               , Code
        FROM @r
    ) p PIVOT ( Code
        FOR [date] IN ('1-2-2011','2-2-2011')
    ) AS pvt
 ORDER BY [Id]

Upvotes: 0

Views: 204

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

The correct PIVOT query would be something like this

declare @r table (Mobile int, Calldate datetime, Dispo varchar(10))
insert @r select
1, '2011-02-21', 'Code1' union all select
2, '2011-02-22', 'Code2'

SELECT ID, [2011-02-21], [2011-02-22]
-- SELECT *   << or just use this, which includes all columns
FROM (
    SELECT Id, Date, Code
    FROM @r) p
PIVOT (MAX(Code) FOR Date IN ([2011-02-21], [2011-02-22])) AS pvt
ORDER BY ID

Your query used

SELECT [Id], '1-2-2011','2-2-2011'

Which includes two FIXED-VALUE strings, which means the DATA is the string '1-2-2011', not a column name. You also needed MAX(Code) or some aggregate function to use when pivoting.

Pivoting in SQL Server requires you to know the columns in advances, which you need to list out in the FOR () bit. Otherwise, you will need to look at dynamic pivoting. This is true whether you use the PIVOT operator or the MAX(CASE pattern to pivot.

The MAX(CASE) pattern

select id,
 MAX(case when date = '2011-02-21' then Code end) "2011-02-21",
 MAX(case when date = '2011-02-22' then Code end) "2011-02-22"
from @r
group by id

Upvotes: 1

Related Questions