question

wisetechacer avatar image
wisetechacer asked

hi ! im new here ; talking about pivot

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 ..... .... ....



pivot
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Jeff Moden avatar image
Jeff Moden answered

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.


https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1


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
;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.