CODEWITHSUNDEEP
sql-servert-sql
hunt3r87
hunt3r87

Reputation: 13

T SQL: Select each matched row as column

I'm new to SQL Server and have a problem

Here is my baseline situation:

Table foo:

id | pid  | val1 | val2 | val3
------------------------------
1  | 4721 |  1   |  2   |  3
2  |   25 |  4   |  5   |  6
3  | 4721 |  7   |  8   |  9

Result table:

pid | id_1 | val1_1 | val2_1 | val3_1 | id_2 | val1_2 | val2_2 | val3_2 | id_3 | val1_3 | val2_3 | val3_3
----------------------------------------------------
4721 | 1  |  1   |  2   |  3  | 2 | 4 | 5 | 6 | 3 | 7 | 8 | 9

What I want is to select all matched rows to pid = 4721, and show them in a separate column.

Upvotes: 1

Views: 81

Answers (2)

Bacon Bits
Bacon Bits

Reputation: 32145

Strictly speaking, the SQL answer to this problem is, "This is a display issue. Display is an application problem, not a database problem." Relationally speaking, this is also asking the server to break First Normal Form and create repeating groups, meaning it's almost certainly going to require jumping through a few hoops and will have significant limitations.

The "right way" according to a DBA would be doing something like:

SELECT pid, id, val1, val2, val3
FROM Table
ORDER BY pid, id;

Then, in your application, walk through your result set and format the output as you need it.

You could even include an order for each id within the pid to maybe make it a bit easier:

SELECT pid, 
    id, 
    val1, 
    val2, 
    val3,
    ROW_NUMBER() OVER (PARTITION BY pid, ORDER BY id) AS "id_order"
FROM Table
ORDER BY pid, id;

However, let's say you can't do that.

If you absolutely have to do this with SQL (e.g., your reporting software doesn't handle this kind of thing and it's all you've got) and you know you never have more than 3 id for each pid, you can try something like this:

;WITH Table_id_ordered AS (
    SELECT pid, 
        id, 
        val1, 
        val2, 
        val3,
        ROW_NUMBER() OVER (PARTITION BY pid, ORDER BY id) AS "id_order"
    FROM Table
)
SELECT t1.pid,
    t1.id   as id_1,
    t1.val1 as val1_1,
    t1.val2 as val2_1,
    t1.val3 as val3_1,
    t2.id   as id_2,
    t2.val1 as val1_2,
    t2.val2 as val2_2,
    t2.val3 as val3_2,
    t3.id   as id_3,
    t3.val1 as val1_3,
    t3.val2 as val2_3,
    t3.val3 as val3_3
FROM Table_id_ordered t1
LEFT JOIN Table_id_ordered t2
    ON t2.pid = t1.pid
    AND t2.id_order = t1.id_order + 1
LEFT JOIN Table_id_ordered t3
    ON t3.pid = t2.pid
    AND t3.id_order = t2.id_order + 1
WHERE t1.id_order = 1;

Obviously, that's only good for up to three id for any pid,. As written, it also won't tell you if there's an id in your table that would be in a fouth or fifth. They're just completely absent from the results. The first method I mentioned will always return all the data, and the application can be written to handle that pretty easily.

Dynamic solutions can be created to do this for any number of ids for a pid, but those are significantly more complicated.

Upvotes: 2

Stephan
Stephan

Reputation: 6018

I first Unpivot your data to get your list of columns and values, assign the new column names, then dynamically pivot your data. This should work for any combination of values.

Note: I slightly changed the column names so they would sort properly.

IF OBJECT_ID('tempdb..#yourTable') IS NOT NULL
    DROP TABLE #yourTable;
IF OBJECT_ID('tempdb..#UnpivotTable') IS NOT NULL
    DROP TABLE #UnpivotTable;

SELECT * INTO #yourTable
FROM
(
    SELECT 1,4721,1,2,3
    UNION ALL 
    SELECT 2,25,4,5,6
    UNION ALL
    SELECT 3,4721,7,8,9
) AS  bar(id,pID,val1,val2,val3);

DECLARE @cols VARCHAR(MAX);


SELECT  pID,
        --This is where I create the column names
        CONCAT(ROW_NUMBER() OVER (PARTITION BY pID,col ORDER BY pID,id2),'_',col) NewColName,
        val INTO #UnpivotTable
FROM 
(
    --I need ID2 for ROW_NUMBER() so the NewColNames are applied to the correct values
    SELECT *,id as ID2
    FROM #yourTable
) A
UNPIVOT
(
    val FOR col IN(ID,val1,val2,val3)
) unpvt


--Puts columns in alphabetic order into @cols
SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME(NewColName)
FROM #UnpivotTable
--Group by gets rid of any duplicate column names
GROUP BY NewColName
ORDER BY NewColName

EXEC
(
    'SELECT *
    FROM #UnpivotTable
    PIVOT
    (
        MAX(val) FOR newColName IN (' + @cols + ')
    ) pvt
    WHERE pID = 4721'
)

--Cleanup
IF OBJECT_ID('tempdb..#yourTable') IS NOT NULL
    DROP TABLE #yourTable;
IF OBJECT_ID('tempdb..#UnpivotTable') IS NOT NULL
    DROP TABLE #UnpivotTable;

Results:

pID         1_id        1_val1      1_val2      1_val3      2_id        2_val1      2_val2      2_val3
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
4721        1           1           2           3           3           7           8           9

Upvotes: 0

Related Questions