RyanL RyanL - 8 days ago 5
SQL Question

Transpose row value to additional pre-defined field when value is less than specified value

I have a table that contains two identifying columns, a date, and a value. This value can be up to 100. What I want to do is where [ID] and [DATE] is the same across subsequent rows and the values are less than 100 (which also means [ID_SECONDARY] is always different), I want a query to place each one of these values in a column '[VALUE_1]...[VALUE_N]' along with the Value Description ([ID_SECONDARY]-->[VALUE_1_DESC]...[VALUE_N_DESC]). Ultimately each row should contain a unique [ID], [DATE], and an aggregation of the different [ID_SECONDARY] descriptions along with their values [VALUE_1]...[VALUE_N]. The number of unique [ID_SECONDARY] will not surpass 4, but could be from 1 to 4.

My initial inclination is to approach this using a cursor, but am hopeful there is a better alternative.

The first image is a sample of the information provided in the table, the second image is the output I'm looking for. Any help is greatly appreciated.

enter image description here

.enter image description here

As far as I can tell this is different from the various dynamic pivot posts out there because the columns are independent of the secondary ID and are fully dependent on the VALUE column to determine if the value itself belongs in columns 1-4.

DVT DVT
Answer

Try this

WITH a AS (
    SELECT
        ID
        , [DATE]
        , ID_SECONDARY
        , VALUE
        , ROW_NUMBER() OVER (PARTITION BY ID, DATE ORDER BY ID) AS RNUM
)
SELECT
    a.ID
    , a.[DATE]
    , MAX (
        CASE a.RNUM
            WHEN 1 THEN a.VALUE
            ELSE NULL
      ) AS VALUE_1
    , MAX (
        CASE a.RNUM
            WHEN 1 THEN a.ID_SECONDARY
            ELSE NULL
    ) AS VALUE_1_DESC
    , MAX (
        CASE a.RNUM
            WHEN 2 THEN a.VALUE
            ELSE NULL
      ) AS VALUE_2
    , MAX (
        CASE a.RNUM
            WHEN 2 THEN a.ID_SECONDARY
            ELSE NULL
    ) AS VALUE_2_DESC
    , MAX (
        CASE RNUM
            WHEN 3 THEN a.VALUE
            ELSE NULL
      ) AS VALUE_3
    , MAX (
        CASE RNUM
            WHEN 3 THEN a.ID_SECONDARY
            ELSE NULL
    ) AS VALUE_3_DESC
    , MAX (
        CASE RNUM
            WHEN 4 THEN a.VALUE
            ELSE NULL
      ) AS VALUE_4
    , MAX (
        CASE RNUM
            WHEN 4 THEN a.ID_SECONDARY
            ELSE NULL
    ) AS VALUE_4_DESC
FROM a
GROUP BY a.ID, a.[DATE]