CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

Convert vertical data to horizontal data with pivot but without aggregate?

This is my data. I'm trying to convert vertical data to horizontal data. I tried using PIVOT, but I don't have a column to be used as aggregate.

I don't need to use PIVOT, but it's the operator I've used for this kind of thing. I also considered it since I know all possible GroupIds.

Name      GroupId
Joe       B1
John      B2
Mary      C1
Lisa      D2
Joe       D2

The result would be something like this. I have to concatenate all the strings for each name:

Name   B1   B2   C1   D2
Joe    B1             D2
John        B2        
Mary             C1
Lisa                  D2

I was thinking of something like this, but I knew it will not produce the desired results.

declare @table table
(
    Name varchar(10),
    GroupId varchar(2)
)

insert into @table
select 'Joe', 'B1' union
select 'John','B2' union
select 'Mary','C1' union
select 'Lisa','D2' union
select 'Joe','D2'

    select *
    from 
    (
      select Name, GroupId
      from @table
    ) src
    pivot
    (
      min(Name)
      for GroupId in ([B1], [B2], [C1], [D2])
    ) piv;

Upvotes: 3

Views: 468

Answers (2)

Slava Murygin
Slava Murygin

Reputation: 1955

;WITH CTE AS (SELECT Name, GroupId, 1 id FROM @table)
select Name
    , [B1] = CASE WHEN [B1] IS NULL THEN '' ELSE 'B1' END
    , [B2] = CASE WHEN [B2] IS NULL THEN '' ELSE 'B2' END
    , [C1] = CASE WHEN [C1] IS NULL THEN '' ELSE 'C1' END
    , [D2] = CASE WHEN [D2] IS NULL THEN '' ELSE 'D2' END
from CTE
PIVOT (min(ID) for GroupId in ([B1], [B2], [C1], [D2])) piv;

OUTPUT

Name       B1   B2   C1   D2
---------- ---- ---- ---- ----
Joe        B1             D2
John            B2        
Lisa                      D2
Mary                 C1   

(4 rows affected)

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use conditional aggregation instead 

select Name,
       max(case when GroupId = 'B1' then GroupId end) [B1],
       max(case when GroupId = 'B2' then GroupId end) [B2],
       max(case when GroupId = 'C1' then GroupId end) [C1],
       max(case when GroupId = 'D2' then GroupId end) [D2]
from @table t
group by Name;

For your current attempt with pivot operator do aggregation with only one column which is GroupId 

select * 
from 
(  
    select Name, GroupId
    from @table
) src pivot (
      max(GroupId)
      for GroupId in ([B1], [B2], [C1], [D2])
 ) piv;

Upvotes: 3

Related Questions