Geek asks and answers

Need help with SQL since I don't have an aggregate column

I have the following view that 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 result to be three lines so that I can create a trend report by week. I can fill in the temporary 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 dataset, I also have a detail of the source article from which the view was created. this dataset is as follows:

techname,machinename,installdate,weeknumber

I made 3 scalar functions that are aggregated by the technome and week number so that I can receive. I have not met, met and exceeded the number of visits for each week.

Thanks in advance.

StuartLC , , , . , , . , .

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
+3
2

StuartLC ( ) , :

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);

, ( 3275636), :

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;
+1

- . univot, :

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;

, ( , ), , sql .

+2

More articles:


All Articles