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

Question

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;

Possible duplicate of [TSQL Pivot without aggregate function](https://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function) — Tab Alleman, May 04 '18 at 17:49

Yogesh Sharma · Answer 1 · 2018-05-04T17:11:50.087

3

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;

edited May 04 '18 at 17:11

answered May 04 '18 at 17:05

Yogesh Sharma

49,870
5
26
52

Thanks, but I assume I would need to use a `group by`? – fdkgfosfskjdlsjdlkfsf May 04 '18 at 17:11

score 0 · Answer 2 · edited May 05 '18 at 11:02

;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)

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

2 Answers2