Need SQL pivot assistance as I do not have an aggregate column

Question

I have the following view I created that looks like this table.

Create Table #Temptable(
[Week number] int,
[Did Not meet minimum] int,
[Met minimum] int,
[exceeded minimum] int);

insert into #Temptable([Week number],[Did Not meet minimum],[Met minimum],[exceeded minimum])
values
(3,161,4,18),
(4,165,1,24),
(5,166,0,10)

I would like the output to be three lines so that I can create a trending report by week. I can populate a temp table to get the desired result but I would like to see if there is a better solution.

Label, week3, week4, week5
Did not meet minimum, 161, 165, 166 
Met minimum, 4, 1, 0
Exceeded minimum, 18, 24, 10

If this is not possible from this data set I also have the source line item detail the view was created from. that dataset looks like this

techname,machinename,installdate,weeknumber

I made 3 scalar functions that aggregate by techname and week number so that I can get the Did not meet, Met, and exceeded install counts for each week.

Thank you in advance.

After getting the response from StuartLC I began to craft my own dynamic version I was close but as I was posting a response another kind person posted a working version. For everyone's benefit here is the sample code I put together it however is not working. If I copy the error text and just select the outputted query it does work properly.

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT distinct ','  + QUOTENAME(c.[week number]) 
        FROM SummarisedWeeklysums c
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'SELECT Criteria, '+ @Cols +' FROM
(
SELECT 
    *
FROM 
    SummarisedWeeklysums
UNPIVOT 
(
   CriteriaCount
   for Criteria in ([Did Not meet minimum],[Met minimum],[exceeded minimum])
) unpvt
) X
  PIVOT
  (
 SUM(CriteriaCount)
 for [Week Number] IN ('+@Cols+')
 )pvt'

 execute @query

score 2 · Answer 1 · edited May 23 '17 at 12:03

This is quite similar to my answer here - basically you are looking for a full transpose. This can be done with an unpivot, followed by a pivot:

SELECT Criteria, [3] as Week3, [4] AS Week4, [5] AS Week5
FROM
(
    SELECT 
        *
    FROM 
        #Temptable
    UNPIVOT 
    (
       CriteriaCount
       for Criteria in ([Did Not meet minimum],[Met minimum],[exceeded minimum])
    ) unpvt
) X
PIVOT
(
 SUM(CriteriaCount)
 for [Week Number] IN ([3],[4],[5])
)pvt;

Fiddle here

Also, if you need to do this for a bunch of unknown weeks (or criteria, I guess), you can look at the answer here which uses dynamic sql to build up the columns and execute the pivot.

Wow.. Thank you so much. I was not really sure how to syntax the pivot command with my dataset. Thank you for the AH HA moment. — user3275636, Feb 05 '14 at 16:17

MKC · Accepted Answer · 2014-02-06T09:10:25.380

The is based on StuartLC answer and link (credit to you) with scalability if extra weeks are added in the future:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME('Week'+cast([Week Number] as varchar))
                      FROM #Temptable
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');
select @query =
'SELECT * 
FROM
(
    SELECT 
          ''Week''+cast([Week number] as varchar) as [Week number] , CriteriaCount ,  Criteria
    FROM 
        #Temptable
    UNPIVOT 
    (
       CriteriaCount
       for Criteria in ([Did Not meet minimum],[Met minimum],[exceeded minimum])
    ) unpvt
) X
PIVOT
(
 SUM(CriteriaCount)
 for [Week Number] in (' + @cols + ')
)pvt ;';
execute(@query);

Below is the same code, with the results moved into a temp table (as requested by user3275636) and then displayed:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME('Week'+cast([Week Number] as varchar))
                      FROM #Temptable
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');
select @query =
'select * into ##temp1 from (SELECT * 
FROM
(
    SELECT 
          ''Week''+cast([Week number] as varchar) as [Week number] , CriteriaCount ,  Criteria
    FROM 
        #Temptable
    UNPIVOT 
    (
       CriteriaCount
       for Criteria in ([Did Not meet minimum],[Met minimum],[exceeded minimum])
    ) unpvt
) X
PIVOT
(
 SUM(CriteriaCount)
 for [Week Number] in (' + @cols + ')
)pvt ) as combi;';
execute(@query);
select * from ##temp1;

Thank you so much I was working on my own based on the link StuartLC sent. Here is what I got it wasn't running properly But if I selected the error and removed the erroneous message info it ran properly. — user3275636, Feb 05 '14 at 20:19
What is the best way to get this into a view or temp table I can query from tableau. — user3275636, Feb 05 '14 at 21:55

Need SQL pivot assistance as I do not have an aggregate column

2 Answers2