Sql server: How to PIVOT Row To Column Header?

Question

I want PIVOT Multiple data row to column By fix column area to header column (3 area is cd,gk,kl)

Example Table: details_data

code | name   | team | score | rank | area
w001 | name01 | AA01 | 10    | 1    | CD
w002 | name02 | AA04 | 05    | 5    | CD
w008 | name08 | AA02 | 08    | 2    | GK
w011 | name11 | AA05 | 15    | 3    | GK
w014 | name14 | AA06 | 10    | 1    | KL
w020 | name20 | AA07 | 20    | 5    | KL

I want re-change column (cd,gk,kl) to header:

cd_code|cd_name|cd_team|cd_score|cd_rank|gk_code|gk_name|gk_team|gk_score|kl......
w001   |name01 | AA01  | 10     | 1     |w008   |name08 | AA02  | 08     |kl......
w002   |name02 | AA04  | 05     | 5     |w011   |name11 | AA05  | 15     |kl......    
................
................

i want pivot row area to column header. Please help me. Thanks advance ;)

Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Mark Rotteveel, Aug 31 '17 at 15:20
Based on your samples output, PIVOT is not what you need. Pivot will turn your rows values into COLUMNS with matching criteria. Your desired output has no correlation between the rows and the columns you added (For ex: w001 and w008 are now on first row). I suggest you clean up your table sample and show us 2 tables you want that you need to convert to Columns. — Isaiah3015, Aug 31 '17 at 15:26
The desired output makes no sense here. Why is w001 and 1008 on the same row? — Sean Lange, Aug 31 '17 at 15:37

score 1 · Answer 1 · answered Aug 31 '17 at 15:44

Perhaps something like this?

Example

;with cte0 as (
    Select * 
          ,CN = Dense_Rank() over (Order by area) 
          ,RN = Row_Number() over (Partition by area Order by area,Code)
     From  details_data 
     )
Select cd_code  = max(case when CN=1 then code end)
      ,cd_name  = max(case when CN=1 then name end)
      ,cd_team  = max(case when CN=1 then team end)
      ,cd_score = max(case when CN=1 then score end)
      ,cd_rank  = max(case when CN=1 then [rank] end)
      ,gk_code  = max(case when CN=2 then code end)
      ,gk_name  = max(case when CN=2 then name end)
      ,gk_team  = max(case when CN=2 then team end)
      ,gk_score = max(case when CN=2 then score end)
      ,gk_rank  = max(case when CN=2 then [rank] end)
      ,kl_code  = max(case when CN=3 then code end)
      ,kl_name  = max(case when CN=3 then name end)
      ,kl_team  = max(case when CN=3 then team end)
      ,kl_score = max(case when CN=3 then score end)
      ,kl_rank  = max(case when CN=3 then [rank] end)
 From  cte0
 Group By RN

Returns

Sql server: How to PIVOT Row To Column Header?

1 Answers1