CODEWITHSUNDEEP
sql-serverpivotmaxaggregation
Phil
Phil

Reputation: 3

SQL Pivot without aggregation

I have a Problem converting rows in columns of a SQL Result.

My structure is like this:

GUID | PropertyName | PropertyValue

abcd | DistinguishedName | cn=abcd...

abcd| CN | cn= GROUP_

abcd| Operation | ADD

1231 | DistinguishedName| cn=123dd

1231 | Cn | cn=ASDGRUOP

1231 | Operation | DEL

There can be n PropertyNames that I dont know before, they are dynamically - i can get them through an SQL, that is not the Problem.

I want to have a structure like this: GUID | DistinguishedName | CN | Operation

abcd| cn=abcd...| cn= GROUP_ | ADD

1231 | cn=123dd | cn=ADSGROUP | DEL

and so on.

The Column-Headers i get by this SQL:

select @cols = STUFF (( SELECT DISTINCT '],[' + x.ParameterName  from ... and parametername in ('PropertyValue','DistinguishedName', 'Operation')
FOR XML PATH ('')),1,2,'') + ']'

I can do this with PIVOT-Function but because i dont have Aggregation, I cant get the right result:

set @query = N'SELECT '+ @cols + ' FROM (
    SELECT x.parametervalue, x.parametername
    from ... and parametername in (''PropertyValue'',''DistinguishedName'', ''Operation'')
    ) a
    PIVOT (max(a.parametervalue) FOR ParameterName in ( ' + @cols + ')) as pv;' 
exec sp_executesql @query;

I get the following result:

GUID | DistinguishedName | CN | Operation | ... other Propertys

abcd | cn=abcd... | cn = GROUP_ |ADD |...

Only 1 Result - not more. But there are like 700 Results from this query, because of the MAX() function i get only one. How can I get a Pivot without Aggregation to get all of the results?

Thank you in Advance!

Upvotes: 0

Views: 88

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Here is the dynamic PIVOT query:

DECLARE @sql NVARCHAR(MAX),
        @cols NVARCHAR(MAX);

SELECT @cols = 
    STUFF((
        SELECT DISTINCT ',' + QUOTENAME(PropertyName) 
        FROM #tbl
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    , 1, 1, '')

SELECT @sql = 
'SELECT GUID, ' + @cols + '
FROM (
    SELECT
        GUID, PropertyName, PropertyValue
    FROM #tbl
) t
PIVOT
(
    MAX(PropertyValue)
    FOR PropertyName IN(' + @cols + ')
) p ';

PRINT (@sql);
EXEC (@sql);

ONLINE DEMO

Another way to achieve the desired result is to use a dynamic crosstab:

DECLARE @sql NVARCHAR(MAX);

SELECT @sql =
'SELECT
    GUID' + CHAR(10) +
(SELECT DISTINCT
'   , MAX(CASE WHEN PropertyName = ''' + PropertyName + ''' THEN PropertyValue END) AS ' + QUOTENAME(PropertyName) + CHAR(10)
FROM #tbl
FOR XML PATH('')
) +
'FROM #tbl
GROUP BY GUID;';

PRINT (@sql);
EXEC (@sql);

ONLINE DEMO

Upvotes: 1

Related Questions