SQL Pivot - Dynamic Columns, No Aggregation

Question

I'm trying to do a Pivot, but I'm not very experienced with pivots and I'm stuck - I can't figure out how to structure the query.

What I have:

Data Types (types of measurements that are recorded)
Locations
Data Sources (things at each location that will be measured)
Data Readings (measurements of the sources)

Additional information:

The number of Sources at any one Location can change
There will never be more than 5 sources at a single Location
Only 1 Reading is saved per Source/Type/date

In the returned table:

Table shows Data_Type info and Readings for a single Location and date
Columns: Data_Name, Units, Is_Required (from Data_Type table), plus one column for each Source
Rows: one row for each Data_Type
Rows should be ordered by Type_Display_Order
Sources (extra columns) should be ordered by Source_Display_Order
Some Readings are optional, and some Sources aren't measured daily - these still need to be included in the table

Example:

Table: Data_Type
Data_Type_ID  Data_Name  Units  Is_Required (BIT)  Type_Display_Order
-----------------------------------------------------------------------
1             Height     In.    1                  2
2             Length     In.    0                  3
3             Weight     Lbs.   1                  1

Table: Location
Location_ID  Location
-----------------------
1            West
2            East

Table: Data_Source
Data_Source_ID  Location_ID  Source_Name  Source_Display_Order
----------------------------------------------------------------
1               1            WCS          2
2               2            ECS          1
3               1            WBN          1

Table: Data_Reading
Data_Reading_ID  Data_Type_ID  Data_Source_ID  Reading  Reading_Date
----------------------------------------------------------------------
1                1             1               5        6/3/2016
2                3             2               3        5/1/2016
3                1             1               7        5/1/2016
4                2             3               2        6/3/2016
5                3             1               4        6/3/2016

Desired results from query for Location = "West", Date = 6/3/2016:

Data_Type_ID  Data_Name  Units  Is_Required  WBN   WCS
---------------------------------------------------------
3             Weight     Lbs.   1            NULL  4
1             Height     In.    1            NULL  5
2             Length     In.    0            NULL  NULL

This solution seems to be similar: Pivot Dynamic Columns, no Aggregation but I'm still having some problems.

This is what I have so far:

DECLARE @date DATE, @locationID INT

SET @date = CAST('6/3/2016' AS DATE)
SET @locationID = 1

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(s.Source_Name)
            FROM Data_Source s
            WHERE s.Location_ID = @locationID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

SET @query = 'SELECT Data_Type_ID, Data_Name, Units, Is_Required, ' + @cols +
' FROM
(
    SELECT
          t.Data_Type_ID
        , t.Data_Name
        , t.Units
        , t.Is_Required
        , r.Reading
        , s.Source_Name
    FROM
        Data_Type t
    LEFT JOIN
        Data_Reading r ON t.Data_Type_ID = r.Data_Type_ID
    LEFT JOIN
        Data_Source s ON r.Data_Source_ID = s.Data_Source_ID
    WHERE
        r.Reading_Date = CAST(CAST(' + @date + ' AS NVARCHAR(10)) AS DATE)
        AND s.Location_ID = CAST(' + @locationID + ' AS INT)
) x
PIVOT
(
    MIN(Reading)
    for Source_Name in (' + @cols + ')
) p '

I have the query working properly now, but I still have a few problems:

@cols is not sorted by Source_Display_Order
rows are not sorted by Type_Display_Order (I did have ORDER BY in the inner SELECT statement for part X, but I was getting errors saying I can't have an ORDER BY clause there)
Date comparison in WHERE statement doesn't work - for some reason, it always computes as False, even when the dates are the same

is the result form query what you wanted? if not what is the expected results. I'm trying to understand what you're trying to query. It would help if we can see what the correct result should look like. — chungtinhlakho, Jun 03 '16 at 21:41
A sample desired result is included above - I modified the title to be in bold font to make it easier to find. Also, I do now have the query returning results without errors, but it still isn't doing what I need - final issues summarized in bullet points at the end. — steelerose, Jun 03 '16 at 21:57
last question, the 4 and 5 in your results. how did you come with that? — chungtinhlakho, Jun 03 '16 at 22:09

score 0 · Answer 1 · answered Jun 03 '16 at 22:31

Solved!

DECLARE @date DATE, @locationID INT

SET @date = CAST('6/3/2016' AS DATE)
SET @locationID = 1

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

SET @cols = STUFF((SELECT ',' + QUOTENAME(s.Source_Name)
            FROM Data_Source s
            WHERE s.Location_ID = @locationID
            ORDER BY s.Source_Display_Order
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

SET @query =
'SELECT
      Data_Type_ID
    , Data_Name
    , Units
    , Is_Required
    , ' + @cols + '
FROM
(
    SELECT
          t.Data_Type_ID
        , t.Data_Name
        , t.Units
        , t.Is_Required
        , r.Reading
        , s.Source_Name
        , t.Type_Display_Order
    FROM
        Data_Type t
    LEFT JOIN
        Data_Reading r ON t.Data_Type_ID = r.Data_Type_ID
    LEFT JOIN
        Data_Source s ON r.Data_Source_ID = s.Data_Source_ID
    WHERE
        r.Reading_Date = ''' + CAST(@date AS NVARCHAR(10)) + '''
        AND s.Location_ID = ' + CAST(@locationID AS NVARCHAR(20)) + '
) x
PIVOT
(
    MIN(Reading)
    for Source_Name in (' + @cols + ')
) p
ORDER BY
    Type_Display_Order'

EXECUTE(@query)

To fix my problems:

Convert @date to NVARCHAR before adding to @query string and include extra quotes to surround the new NVARCHAR in quotes within @query
Remove DISTINCT clause from @cols and add ORDER BY (all of the names in my table are unique, so the DISTINCT is unnecessary)
Add Type_Display_Order to the inner SELECT statement, and add ORDER BY after the PIVOT statement

SQL Pivot - Dynamic Columns, No Aggregation

1 Answers1