0

I understand this question has been asked numerous times however I can't seem to find an example that fits, or works, in my situation. I have a query that outputs data in rows however I need it by columns. The pivot column is an item description and the amount of columns can vary. I have the code below which builds the columns, this is working correctly.

    DECLARE @Columns as VARCHAR(MAX)
    SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME(JOB_GROUP)
    FROM
        (SELECT DISTINCT ITEM.ITEM_DESCRIPTION
         FROM ITEM LEFT JOIN MATRIX_DETAIL ON ITEM.ITEM_ID= MATRIX_DETAIL.ITEM_ID
         WHERE MATRIX_DETAIL.MATRIX_ID = 1
        ) AS B
        ORDER BY B.ITEM_DESCRIPTION

The code below is where I'm having issues. I'm not sure if it's the joins or what is going. For the final report it will have quite a few more columns before the pivoted columns start, for this example I'm just trying to get one column to appear correctly and then I can build it out from there. The code below is telling me I've got invalid column names, etc. I'm missing something obvious, and hopefully easy, in this. Any help would be appreciated.

       DECLARE @SQL as VARCHAR(MAX)

       SET @SQL  = 'SELECT ACTION_CODE_KIT, ' + @Columns + '
         FROM 
         (SELECT MATRIX.ACTION_CODE_KIT
          FROM MATRIX INNER JOIN
               MATRIX_ACTION_CODES ON MATRIX.MATRIX_ID = MATRIX_ACTION_CODES.MATRIX_ID INNER JOIN
               MATRIX_DETAIL ON MATRIX_ACTION_CODES.ACTION_CODE_ID = MATRIX_DETAIL.ACTION_CODE_ID LEFT OUTER JOIN
               ITEM ON MATRIX_DETAIL.ITEM_ID = ITEM.ITEM_ID LEFT OUTER JOIN
               [PLAN] ON MATRIX_ACTION_CODES.PLAN_CODE = [PLAN].PLAN_CODE
         ) as PivotData
         PIVOT
         (
            MIN(ITEM_ID)
            FOR MATRIX.ACTION_CODE_KIT IN(' + @Columns + ')
         ) AS PivotResult'

         EXEC (@sql)
Steve
  • 1
  • 6
  • I would start by printing the `@sql` and trying to execute that query. It will give you a more clear idea. – Rigerta Dec 01 '17 at 14:11
  • I did do that which is where I was getting the errors. It's stating invalid column name 'ACTION_CODE_KIT', invalid column name 'ITEM_ID' and the column prefix 'MATRIX' does not match with a table name. – Steve Dec 01 '17 at 14:14
  • 1
    We need some details here to help. We don't know your table structure and can't see your screen. Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – Sean Lange Dec 01 '17 at 14:16
  • Gotcha, can't really post up the data. All joins are on integer type and the item_id and action_code_kit are both nvarchar. – Steve Dec 01 '17 at 14:20

1 Answers1

0

Pivot is very straight forward.

SQL Server dynamic PIVOT query?

You only have to make sure the INNER JOIN query produce something in the format the pivot needs.

You didnt provide any data or structure so I can give you specific details. But what I would do is Insert the INNER JOIN query in a temporal table first to make sure the data have the right format. Then perform a normal pivot to make sure everything is ok and finally add the dynamic columns .

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • straight forward lol. It was able to point me in the right direction – Steve Dec 01 '17 at 14:44
  • So I have it outputting everything now. I'll need to spend some time validating the results to be sure but all looks good. Just needed some direction cause me banging my head against the same "solutions" I was finding wasn't getting anywhere – Steve Dec 01 '17 at 14:46
  • So it was really straight forward or was a little sarcasm there? My point was if you have the data in the right format is easy to do the pivot. – Juan Carlos Oropeza Dec 01 '17 at 14:48
  • It was a bit of sarcasm. Mainly I wouldn't be here if it was that straight forward. However, as I said your comments got me looking in a different direction than I had been and allowed me to start knocking the errors off and actually outputting data. So thanks for the input because I'd still be searching otherwise. – Steve Dec 01 '17 at 14:52