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

Question

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.

.

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.

What is the purpose of formatting the data in this way? It can certainly be achieved but it seems like you are trying to do something that is best handled somewhere else. — iamdave, Dec 01 '16 at 16:17
Users of the data prefer the single row display to see the identifiers and values at any given date for any given ID. They find the multiple rows for each ID onerous and difficult to navigate and write queries against. — RyanL, Dec 01 '16 at 16:24
Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Matt, Dec 01 '16 at 16:28
you are looking for dynamic pivot because you will have N values. If you know your number of values ahead of time you could just use pivot. The trick is to use ROW_NUMBER() to generate a new field that you will use to identify which value should go into which column — Matt, Dec 01 '16 at 16:29
What application are your users consuming this data through? Can that application not handle the layout and formatting. For example, in SQL Server Reporting Services this would be very easily handled via row and column grouping. — iamdave, Dec 01 '16 at 16:31
All good points, thank you. The number of secondary identifiers will not exceed 4, so there is a total # of value fields=4. Users will consume this data via MS Access. — RyanL, Dec 01 '16 at 16:38
Can you not then format this using an Access report or form control? — iamdave, Dec 01 '16 at 16:43
Users will generally access this for ad-hoc type reporting that will join with various other tables. Access reports/forms won't fully satisfy the requirement. — RyanL, Dec 01 '16 at 16:45
From what I understand, since the number of unique values of the secondary ID is only 4, and assume that the ID, ID_SECONDARY, DATE uniquely define a row in the first table, the result table cannot have more than 10 columns? (ID, DATE, VALUE_1, VALUE_DESCRIPTION_1, .., VALUE_4, VALUE_DESCRIPTION_4) — DVT, Dec 01 '16 at 16:59

DVT · Accepted Answer · 2016-12-01T17:37:32.233

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]

Brilliant, thank you @DVT. I modified the row_number partition to [DATE] FROM [ID_SECONDARY] as the initial results placed each subsequent [ID_SECONDARY] in the wrong column. — RyanL, Dec 01 '16 at 17:29

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

1 Answers1