Pivot table without aggregation and multiple pivot columns

Question

I've got this situation where I think the best solution is using a pivot function, but I don't really know how to. Or maybe there is a much better way to do this...

I got this table as a source :

ID   | version | code | property | value
-----|---------|------|----------|------
  110|        1|    AA|     prop1|    10
  110|        1|    AA|     prop2|    20
  110|        1|    AA|     prop3|    30
  110|        1|    BB|     prop1|    10
  110|        1|    BB|     prop2|    40
  110|        1|    BB|     prop3|    20
  110|        2|    AA|     prop1|    50
  120|        1|    BB|     prop2|    60
  120|        2|    AA|     prop3|    80

What I want to end up with is the following :

ID   | version | code | prop1 | prop2 | prop3
-----|---------|------|-------|-------|------
  110|        1|    AA|     10|     20|    30
  110|        1|    BB|     10|     40|    20
  110|        2|    AA|     50|       |      
  120|        1|    BB|       |     60|      
  120|        2|    AA|       |       |    80

So you see I don't do aggregation, just a pivot over the first 3 tables. Is this posible in TSQL, I'm using SQL Server 2012.

"No aggregation" is easily achieved if there will only ever be a maximum of one value in each group and you pick an aggregate which, if provided a single value, returns that same value - such as SUM, MIN or MAX (I generally prefer the latter two since they apply to a wider range of types) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Apr 21, 2015 at 7:32

Giorgi Nakeuri · Accepted Answer · 2015-04-21 07:38:43Z

2

You want grouping on id, version and code, so it is simple pivot:

Select * from TableName
pivot(max(value) for property in([prop1],[prop2],[prop3]))p

If table has more columns then you will need to pick right columns first:

;with cte as(Select id, version, code, property, value from TableName)
Select * from cte
pivot(max(value) for property in([prop1],[prop2],[prop3]))p

answered Apr 21, 2015 at 7:38

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Felix Pamittan · Accepted Answer · 2015-04-21 07:38:55Z

If the property is always prop1, prop2 and prop3, you could do this using conditional aggregation:

SELECT
      ID
    , Version
    , Code
    , MAX(CASE WHEN Property = 'prop1' THEN VALUE END) AS [prop1]
    , MAX(CASE WHEN Property = 'prop2' THEN VALUE END) AS [prop2]
    , MAX(CASE WHEN Property = 'prop3' THEN VALUE END) AS [prop3]
FROM SampleData
GROUP BY ID, Version, Code

Here is a dynamic approach. Read this article for reference.

SQL Fiddle

DECLARE @sql1 VARCHAR(4000) = ''
DECLARE @sql2 VARCHAR(4000) = ''
DECLARE @sql3 VARCHAR(4000) = ''

SELECT @sql1 = 
'SELECT
      ID
    , Version
    , Code
'

SELECT @sql2 = @sql2 +
'   , MAX(CASE WHEN Property = ''' + Property + ''' THEN VALUE END) AS [' + Property + ']' + CHAR(10)
FROM(
    SELECT DISTINCT Property FROM SampleData
)t
ORDER BY Property

SELECT @sql3 =
'FROM SampleData
GROUP BY ID, Version, Code
ORDER BY ID, Version, Code'

PRINT(@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)

mohan111 · Accepted Answer · 2015-04-21 07:44:54Z

1

declare @t table (Id int,version int,code varchar(10),property varchar(10),val int)
insert into @t (Id,version,code,property,val)values (110,1,'AA','prop1',10),
(110,1,'AA','prop2',20),(110,1,'AA','prop3',30),(110,1,'BB','prop1',10),
(110,1,'BB','prop2',40),(110,1,'BB','prop3',20),(110,2,'AA','prop1',50),(120,1,'BB','prop2',60),
(120,1,'AA','prop3',80)


select Id,version,code,[prop1],[prop2],[prop3]
  from (
select ID,version,code,property,val 
    from @t )t
PIVOT(SUM(val)FOR Property IN ([prop1],[prop2],[prop3]))p

answered Apr 21, 2015 at 7:44

mohan111

8,8854 gold badges35 silver badges61 bronze badges

Collectives™ on Stack Overflow

Pivot table without aggregation and multiple pivot columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related