hi ! i need help about pivot in SQL ; I need to know how to convert N rows to columns in SQL without using Pivot ;
sample :
name value id
1 name Rayan 1
2 gender male 1
3 salary 1000 1
..... ..... ...... ......
.
N xxx valueN IdN
to obtain :
id name Gender Salary
1 Rayan male 1000
N ..... .... ....
To do it without the word PIVOT, you need to use a CROSSTAB. Here's a link that explains how it works. It's a powerful, old, "Black Arts" tool and you should read the article.
Here's the code to solve the problem you posted.
SELECT ID ,Name = MAX(CASE WHEN name = 'name' THEN value ELSE '' END) ,Gender = MAX(CASE WHEN name = 'gender' THEN value ELSE '' END) ,Salary = MAX(CASE WHEN name = 'salary' THEN CONVERT(INT,value) ELSE '' END) FROM dbo.Sample GROUP BY ID ORDER BY Name ;
18 People are following this question.
