How to pivot two of the columns in my SQL Query

Question

This query is almost there. The only problem I have now is that the results are not pivoting. Instead they stay in the column named Value.

SELECT * FROM (
    SELECT
        jD.AccountID, 
        SUM(jD.Amount) AS [Total Dollars], 
        COA.Name as COAName, 
        SUM(jD.Qty) AS QTY, 
        CONVERT(date, GETDATE()) AS Date, 
        AttributeDefinitions.Name, 
        AttributeValues.Value
    FROM
        AttributeDefinitions INNER JOIN
        AttributeCategories ON AttributeDefinitions.AttributeCategoryID = AttributeCategories.AttributeCategoryID INNER JOIN
        AttributeValues ON AttributeDefinitions.AttributeDefinitionID = AttributeValues.AttributeDefinitionID RIGHT OUTER JOIN
        JnlDetails AS jD WITH (NOLOCK) INNER JOIN
        COA ON jD.AccountID = COA.AccountID ON AttributeValues.AttributeValueGroupID = COA.AttributeValueGroupID
    WHERE
        (jD.CreateDate >= GETDATE() - 2) AND 
        (jD.CreateDate < GETDATE() + 1)
    GROUP BY 
        jD.AccountID, 
        COA.Name, 
        jD.Qty, 
        jD.CreateDate, 
        AttributeDefinitions.Name, 
        AttributeValues.Value
   
) as T1
PIVOT
(
   MAX([Name]) FOR Name IN ([ACCT_NO], [DEPT_ID], [GLENTRY_CLASSID],[GLENTRY_PROJECTID]
   ,[GLDIMBENEFITING_DEPARTMENT],[GLDIMFUND],[LOCATION_ID])
) PT

This seems like a simple `PIVOT`, have you tried writing it? Should be like `PIVOT (MAX([Value]) FOR Name IN ([1 Account Code], [2 Project Code], etc) AS P` — EzLo, Feb 12 '19 at 08:04
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server?](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Thom A, Feb 12 '19 at 08:17
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — EzLo, Feb 12 '19 at 08:43
I tried writing it but it wouldn't work for me . the biggest problem i have is that i have a mixture of columns that im pivoting and not pivoting and also the number of rows vary. but im testing out some of the suggestions — Luis A, Feb 13 '19 at 08:03

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-02-13T08:33:29.497

Is this what you are looking for ?

SELECT * FROM (
    SELECT
        jD.AccountID, 
        SUM(jD.Amount) AS [Total Dollars], 
        COA.Name, 
        SUM(jD.Qty) AS QTY, 
        CONVERT(date, GETDATE()) AS Date, 
        AttributeDefinitions.Name, 
        AttributeValues.Value
    FROM
        AttributeDefinitions INNER JOIN
        AttributeCategories ON AttributeDefinitions.AttributeCategoryID = AttributeCategories.AttributeCategoryID INNER JOIN
        AttributeValues ON AttributeDefinitions.AttributeDefinitionID = AttributeValues.AttributeDefinitionID RIGHT OUTER JOIN
        JnlDetails AS jD WITH (NOLOCK) INNER JOIN
        COA ON jD.AccountID = COA.AccountID ON AttributeValues.AttributeValueGroupID = COA.AttributeValueGroupID
    WHERE
        (jD.CreateDate >= GETDATE() - 2) AND 
        (jD.CreateDate < GETDATE() + 1)
    GROUP BY 
        jD.AccountID, 
        COA.Name, 
        jD.Qty, 
        jD.CreateDate, 
        AttributeDefinitions.Name, 
        AttributeDefinitions.Description, 
        AttributeValues.Value
    ORDER BY 
        jD.AccountID
) as T1
PIVOT
(
   MAX([Name]) FOR Name IN ([Account Code], [Project Code], [Fund])
) PT

This is not working for me , i get a red line under Pivot and name at the very bottom. if i hoover over pivot it says "Incorrect syntac near pivot. expecting AS,ID, or Quoted_ID. — Luis A, Feb 13 '19 at 08:19
I dont get an error now so thats great but its still not working . the problem is that the fields are empty. the data is not pivoting — Luis A, Feb 21 '19 at 10:16

Daniel Brughera · Answer 2 · 2019-02-21T11:39:45.807

Please note that the results vary , so i can have 3 columns , or 4 , 5 , etc... up to 10 columns

Then you need dynamic SQL

DECLARE @Attributes VARCHAR(MAX), @SQL VARCHAR(MAX)

SELECT @Attributes = STUFF((SELECT ',' + QUOTENAME(Name) 
                    FROM AttributeDefinitions
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,1,'')

SET @SQL = '
SELECT * FROM (
    SELECT
        jD.AccountID, 
        SUM(jD.Amount) AS [Total Dollars], 
        COA.Name, 
        SUM(jD.Qty) AS QTY, 
        CONVERT(date, GETDATE()) AS Date, 
        AttributeDefinitions.Name, 
        AttributeValues.Value
    FROM
        AttributeDefinitions INNER JOIN
        AttributeCategories ON AttributeDefinitions.AttributeCategoryID = AttributeCategories.AttributeCategoryID INNER JOIN
        AttributeValues ON AttributeDefinitions.AttributeDefinitionID = AttributeValues.AttributeDefinitionID RIGHT OUTER JOIN
        JnlDetails AS jD WITH (NOLOCK) INNER JOIN
        COA ON jD.AccountID = COA.AccountID ON AttributeValues.AttributeValueGroupID = COA.AttributeValueGroupID
    WHERE
        (jD.CreateDate >= GETDATE() - 2) AND 
        (jD.CreateDate < GETDATE() + 1)
    GROUP BY 
        jD.AccountID, 
        COA.Name, 
        jD.Qty, 
        jD.CreateDate, 
        AttributeDefinitions.Name, 
        AttributeDefinitions.Description, 
        AttributeValues.Value
    ORDER BY 
        jD.AccountID
) T
PIVOT
(
   MAX([Name]) FOR Name IN (' + @Attributes + ')
) PT
'

EXEC(@SQL);

This is not working for me , i get a red line under Pivot and name at the very bottom. if i hoover over pivot it says "Incorrect syntac near pivot. expecting AS,ID, or Quoted_ID. — Luis A, Feb 13 '19 at 08:21

How to pivot two of the columns in my SQL Query

2 Answers2