Home > SQL > Pivoting Data in SQL

Pivoting Data in SQL

I believe that may of you came across the need to pivot, or simply rotate some SQL data from columns to rows, while working with dynamic attributes or key-value pares tables.

For example I have the following database structure:



And of cause each object can have various number of associated attributes. Assuming that I need to fetch all attributes from the relevant objects, while attributes are aligned to the object in the same row, it becomes quite a trivia to perform.


In my example tables look simple and have only the following data:

T-SQL indeed has a PIVOT clause, but it would work only with aggregation and when the number of columns is well know up front.

There are couple of techniques to resolve this without using the PIVOT.

  • When desired columns are known,  a simple statement like the one below, will do the trick:
SELECT NAME, id, MAX(A) A, MAX(B) B, MAX(C) C

from
SELECT name, dbo.tblObject.id,

CASE vcKey WHEN 'A' THEN vcValue ELSE NULL END 'A',

CASE vcKey WHEN 'B' THEN vcValue ELSE NULL END 'B',

CASE vcKey WHEN 'C' THEN vcValue ELSE NULL END 'C'
FROM dbo.tblObject INNER JOIN dbo.tblAttributes
ON dbo.tblObject.id = tblAttributes.ID)TBL
GROUP BY NAME, id
  • When the list of attributes is dynamic, our solution becomes a bit more complicated, but does the job in a very elegant and efficient way
DECLARE @vcKey VARCHAR(100)

DECLARE @MainSql nVARCHAR(1000)

DECLARE @CaseSql nVARCHAR(1000)

-- Fixed columns schema goes here

SET @MainSql = 'SELECT NAME, id'

SET @CaseSql = ''

DECLARE CUR CURSOR FAST_FORWARD READ_ONLY FOR

SELECT DISTINCT vcKey

FROM dbo.tblAttributes WITH (NOLOCK)

WHERE id IN (1,2)

OPEN CUR

FETCH NEXT FROM CUR INTO @vcKey

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @vcKey

-- append new column to the results schema

SET @MainSql = @MainSql + ',Max(' + @vcKey + ') ['+ @vcKey +']'

-- append next CASE statement to the CASE schema

SET @CaseSql = @CaseSql + '

CASE vcKey WHEN '''+ @vcKey +''' THEN vcValue ELSE NULL END '''+ @vcKey +''','

FETCH NEXT FROM CUR INTO @vcKey;

END;

-- format the global statement

SET @MainSql = @MainSql + '

from

(SELECT name, dbo.tblObject.id, ' + SUBSTRING(@CaseSql, 1, LEN(@CaseSql)-1) +

'

FROM dbo.tblObject INNER JOIN dbo.tblAttributes

ON dbo.tblObject.id = tblAttributes.ID) TBL

GROUP BY NAME, id'

CLOSE CUR;

DEALLOCATE CUR;

print @MainSql

EXEC sp_executesql @MainSql
Categories: SQL Tags: , , ,
  1. No comments yet.
  1. No trackbacks yet.