SQL Server - Possible Pivot Solution

SQL pivoting - How to get all possible combinations between multiple columns to be pivoted

This is trickier than your previous question, because not all ids have all the values.

The temptation is:

select pd1.nct_id, pd1.parameter_value as asset, pd2.parameter_value as indication,
       pd3.parameter_value as BMx, pd4.parameter_value as LOT
from pivot_dummy pd1 join
     pivot_dummy pd2
     on pd1.nct_id = pd2.nct_id and
        pd1.parameter_type = 'Asset' and
        pd2.parameter_type = 'Indication' join
     pivot_dummy pd3
     on pd1.nct_id = pd3.nct_id and
        pd3.parameter_type = 'BMx' join
     pivot_dummy pd4
     on pd1.nct_id = pd4.nct_id and
        pd4.parameter_type = 'LOT';

This only works for ids that have all four values. Switching to a full join solves the problem -- but complicates the query.

I think the best solution is a left join that looks like this:

select pd.nct_id, pd1.parameter_value as asset, pd2.parameter_value as indication,
       pd3.parameter_value as BMx, pd4.parameter_value as LOT
from (select distinct nct_id from pivot_dummy
     ) pd left join
     pivot_dummy pd1
     on pd1.nct_id = pd.nct_id and
        pd1.parameter_type = 'Asset' left join
     pivot_dummy pd2
     on pd2.nct_id = pd.nct_id and
        pd2.parameter_type = 'Indication' left join
     pivot_dummy pd3
     on pd3.nct_id = pd.nct_id and
        pd3.parameter_type = 'BMx' left join
     pivot_dummy pd4
     on pd4.nct_id = pd.nct_id and
        pd4.parameter_type = 'LOT';

Simple SQL pivot

The value list defined in the pivot clause must contain actual values from your table. [1], [2], [3] are values from your PersonId, not for DeviceId. So the part for DeviceId in [1], [2], [3] is not producing any results, hence all the null values.

Here is my solution. I constructed a new key_ column to pivot around.

Sample data with added person names

declare @person table
(
    personid int,
    personname nvarchar(100)
);

insert into @person (personid, personname) values
(1, 'Ann'),
(2, 'Britt'),
(3, 'Cedric');

declare @device table
(
    personid int,
    deviceid int
);

insert into @device (personid, deviceid) values
(1, 1111),
(1, 2222),
(1, 3333),
(2,  123),
(2,  456),
(3, 9999);

Solution

Run the CTE part on its own to see the intermediate result table. The key_ column contains values like DEVICE_* which are the same values used in the for key_ in part of the pivot clause.

with base as
(
    select  p.personname,
            d.deviceid,
            'DEVICE_' + convert(char, ROW_NUMBER() over(partition by p.personname order by d.deviceid)) as 'key_'
    from @person p
    join @device d
        on d.personid = p.personid
)
select piv.personname, piv.DEVICE_1, piv.DEVICE_2, piv.DEVICE_3
from base
pivot( max(deviceid) for key_ in ([DEVICE_1], [DEVICE_2], [DEVICE_3]) ) piv;

Result

The intermediate CTE result table

personname deviceid    key_
---------- ----------- ----------
Ann        1111        DEVICE_1                             
Ann        2222        DEVICE_2                             
Ann        3333        DEVICE_3                             
Britt      123         DEVICE_1                             
Britt      456         DEVICE_2                             
Cedric     9999        DEVICE_1   

The final result

personname DEVICE_1    DEVICE_2    DEVICE_3
---------- ----------- ----------- -----------
Ann        1111        2222        3333
Britt      123         456         NULL
Cedric     9999        NULL        NULL

How can I use an SQL Pivot for this?

Same kinda answer here, that was fun:

-- Get column names from system table
DECLARE @phCols NVARCHAR(2000)
SELECT @phCols = COALESCE(@phCols + ',[' + name + ']', '[' + name + ']') 
    FROM syscolumns WHERE id = (select id from sysobjects where name = 'Test' and type='U')

-- Get rid of the column we don't want
SELECT @phCols = REPLACE(@phCols, '[Timestamp],', '')

-- Query & sum using the dynamic column names
DECLARE @exec nvarchar(2000)
SELECT @exec =
'
    select
        SUBSTRING([Value], 2, LEN([Value]) - 1) as [Index],
        SUM(CASE WHEN (LEFT([Value], 1) = ''A'') THEN Cols ELSE 0 END) as AValue, 
        SUM(CASE WHEN (LEFT([Value], 1) = ''B'') THEN Cols ELSE 0 END) as BValue
    FROM
    (
        select *
        from (select ' + @phCols + ' from Test) as t
        unpivot (Cols FOR [Value] in (' + @phCols + ')) as p
    ) _temp
    GROUP BY SUBSTRING([Value], 2, LEN([Value]) - 1)
'
EXECUTE(@exec)

You don't need to hard code column names in this one.

SQL pivot - How to get all possible combinations between columns to be pivoted

From what I can tell, you don't want a pivot at all. Simply a join:

select pd1.nct_id, pd1.parameter_value as asset, pd2.parameter_value as indication
from pivot_dummy pd1 join
     pivot_dummy pd2
     on pd1.nct_id = pd2.nct_id and
        pd1.parameter_type = 'Asset' and
        pd2.parameter_type = 'Indication';

Pivoting tables in SQL Server in 2019 year and future

But the caveat is that it is a stored procedure so it requires a static table as input and it outputs also a static table.

I don't understand this. The table name is one of the arguments, so this does not require a "static table".

More importantly, the stored procedure cannot be written as a (reasonable) user-defined function, because it requires dynamic SQL. And user-functions don't support dynamic SQL.

You also need to realize that anything in the FROM clause requires that the columns and types of columns be known during the compilation phase of the query. This precludes the use of strings to specify column names, because the string could be a parameter.

SQL PIVOT Multiple Records to columns

You will have to make use of dynamic SQL to achieve desired results since the possible values on which the dataset should be pivoted on is unknown at the time of execution. You are also pivoting on multiple columns [Observation] & [Observer] which means you will either have to make use of a CASE statement with a GROUP BY clause or combine the two columns in question into a single pivot value column. What complicates this query further is the fact that the [Type] will seems to impact on how the results should be displayed.

I have created an example of how you can achieve your desired results below. I made the following assumptions:

• Value of type field is defined as follow: 1 = Observation, 2 =
  Interim, 3 = Summative, 6 = Overall

• Based on your sample output only one occurrence of an Interim, Summative and Overall observations is expected in pivoted results.

• Based on your sample output the Observer is not displayed for Interim, Summative and Overall observations

Example:

-- Create some sample data
DECLARE @Observations TABLE
(
     [Name]         NVARCHAR(50) NOT NULL
    ,[Building]     NVARCHAR(50) NOT NULL
    ,[Observation]  DATETIME
    ,[Observer]     NVARCHAR(50) NOT NULL
    ,[Type]         INT
);

INSERT INTO @Observations
SELECT 'Doe, John', 'HQ', '01/01/2017', 'Doe, Jack', 1 UNION ALL
SELECT 'Doe, John', 'HQ', '02/01/2017', 'Doe, Jack', 1 UNION ALL
SELECT 'Doe, John', 'HQ', '03/01/2017', 'Doe, Jack', 2 UNION ALL
SELECT 'Doe, John', 'HQ', '04/01/2017', 'Doe, Jack', 1 UNION ALL
SELECT 'Doe, John', 'HQ', '05/01/2017', 'Doe, Jack', 1 UNION ALL
SELECT 'Doe, John', 'HQ', '06/01/2017', 'Doe, Jack', 3 UNION ALL
SELECT 'Doe, John', 'HQ', '07/01/2017', 'Doe, Jack', 6;

DECLARE @columns NVARCHAR(MAX) = N'';
DECLARE @selectClause NVARCHAR(MAX) = N'';
DECLARE @sql NVARCHAR(MAX);

CREATE TABLE #IntermResults
(
     [Name]         NVARCHAR(50) NOT NULL       
    ,[Building]     NVARCHAR(50) NOT NULL   
    ,[PivotValue]   NVARCHAR(50) NOT NULL   
    ,[PivotID]      NVARCHAR(50) NOT NULL
    ,[Observation]  DATETIME NOT NULL
    ,[OrderRank]    INT NOT NULL
)

-- Prepare the source data to make pivoting into desired format easier and store results into an interim temporary table.
;WITH ObservationsCTE
AS
(
    SELECT   [Name]         
            ,[Building]     
            ,[Observation]  
            ,[Observer]     
            ,[Type]
            ,(  
                CASE [Type]
                    WHEN 1 THEN 'Observation'
                    WHEN 2 THEN 'Interim'
                    WHEN 3 THEN 'Summative'
                    WHEN 6 THEN 'Overall'
                    ELSE 'UNKNOW'
                END
             ) AS [ObservationType] -- Give descriptive values to Type column’s values. This will be used to help generate pivot table column names.
    FROM    @Observations   
), ObservationsWithPivotIDs
AS
(
    SELECT   TOP 1000
             [Name]         
            ,[Building]     
            ,CAST([Observation] AS NVARCHAR(50)) AS [PivotValue] -- Pivot value effectively combines the Observation & Observer column to help simplify the pivot query.
            ,'Observation' + CAST(ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Observation]) AS NVARCHAR(10)) AS [PivotID] -- Create a pivot id that will be used to generate pivoted columns.
            ,[Observation]
            ,1 AS [OrderRank] -- use this field to help order records.
    FROM    ObservationsCTE
    WHERE   [Type] = 1
    UNION ALL
    SELECT   TOP 1000
             [Name]         
            ,[Building]     
            ,[Observer] AS [PivotValue] -- Pivot value effectively combines the Observation & Observer column to help simplify the pivot query.
            ,'Observer' + CAST(ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Observation]) AS NVARCHAR(10)) AS [PivotID] -- Create a pivot id that will be used to generate pivoted columns.
            ,[Observation]
            ,2 AS [OrderRank] -- use this field to help order records.
    FROM    ObservationsCTE
    WHERE   [Type] = 1
    UNION ALL
    SELECT   TOP 1000
             [Name]         
            ,[Building]     
            ,CAST([Observation] AS NVARCHAR(50)) AS [PivotValue] -- Pivot value effectively combines the Observation & Observer column to help simplify the pivot query.
            ,[ObservationType]  AS [PivotID]
            ,[Observation]
            ,1 AS [OrderRank] -- use this field to help order records.
    FROM    ObservationsCTE
    WHERE   [Type] <> 1
)
INSERT INTO #IntermResults  -- Insert the results into an intermediate table.
(
     [Name]         
    ,[Building]     
    ,[PivotValue]   
    ,[PivotID]      
    ,[Observation]  
    ,[OrderRank]    
)
SELECT   [Name]         
        ,[Building]     
        ,[PivotValue]   
        ,[PivotID]      
        ,[Observation]  
        ,[OrderRank]    
FROM    ObservationsWithPivotIDs

-- Determine what columns will be created for the pivot
SELECT   @columns += N', ' + QUOTENAME([PivotID])
        ,@selectClause += N', ' + (CASE [OrderRank]
                                        WHEN  1 THEN 'CAST(' + QUOTENAME([PivotID]) + ' AS DATETIME) AS ' + QUOTENAME([PivotID])
                                        ELSE [PivotID] 
                                    END)
FROM     #IntermResults
ORDER BY [Observation], [OrderRank]

-- Create dynamic query to create the pivot
SET @sql = N'
SELECT [Name], [Building], ' + STUFF(@selectClause, 1, 2, '') + '
FROM (
        SELECT  [Name]
                ,[Building]
                ,[PivotValue]
                ,[PivotID]
        FROM    #IntermResults
    ) i
PIVOT
(
  MAX([PivotValue]) FOR [PivotID] IN ('
  + STUFF(@columns, 1, 1, '')
  + ')
) AS p;';

PRINT @sql;
-- Execute dynamic pivot query
EXEC sp_executesql @sql;
-- Drop intermediate results
DROP TABLE #IntermResults

Convert Rows to columns using 'Pivot' in SQL Server

If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns.

It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.

First up, here are some quick table definitions and data for use:

CREATE TABLE yt 
(
  [Store] int, 
  [Week] int, 
  [xCount] int
);
    
INSERT INTO yt
(
  [Store], 
  [Week], [xCount]
)
VALUES
    (102, 1, 96),
    (101, 1, 138),
    (105, 1, 37),
    (109, 1, 59),
    (101, 2, 282),
    (102, 2, 212),
    (105, 2, 78),
    (109, 2, 97),
    (105, 3, 60),
    (102, 3, 123),
    (101, 3, 220),
    (109, 3, 87);

If your values are known, then you will hard-code the query:

select *
from 
(
  select store, week, xCount
  from yt 
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;

See SQL Demo

Then if you need to generate the week number dynamically, your code will be:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Week) 
                    from yt
                    group by Week
                    order by Week
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT store,' + @cols + ' from 
             (
                select store, week, xCount
                from yt
            ) x
            pivot 
            (
                sum(xCount)
                for week in (' + @cols + ')
            ) p '

execute(@query);

See SQL Demo.

The dynamic version, generates the list of week numbers that should be converted to columns. Both give the same result:

| STORE |   1 |   2 |   3 |
---------------------------
|   101 | 138 | 282 | 220 |
|   102 |  96 | 212 | 123 |
|   105 |  37 |  78 |  60 |
|   109 |  59 |  97 |  87 |

---

Related Topics