For Each Display on Same Row SQL Server

Question

I am trying to create a single row that includes an Assembly number and each component.

I have a table called Assembly with a Unique Identifier that can be tied back to the inv_mast table as well as Components in that table that can be tied back to the same inv_mast table. It also includes a sequence number.

inv_mast_uid |  sequence_number |   component_inv_mast_uid

453061       | 1                | 453024
453061       | 2                | 453017
453061       | 3                | 453020
453062       | 1                | 453019
453062       | 2                | 453027

(Some of my entries only have 2 Components)

What I am trying to achieve is:

inv_mast_uid |  component_inv_mast_uid_1 |  component_inv_mast_uid_2    | component_inv_mast_uid_3
453061       | 453024                    | 453017                       | 453020    
453062       | 453019                    | 453027                       | Null

I was thinking I would use a 'For Each` loop, but I've never had any luck with it and SQL Server

Do you have a fixed maximum number of components or is it dynamic? — Sean Lange, Sep 28 '18 at 15:07
You'd have to use pivot functionality. Here's an example: https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — NonProgrammer, Sep 28 '18 at 15:14

score 6 · Accepted Answer · answered Sep 28 '18 at 15:18

6

What you are trying to do is called Pivot so i hope this helps you

SELECT INV_MAST_UID
     ,[1] AS COMPONENT_INV_MAST_UID_1
     ,[2] AS COMPONENT_INV_MAST_UID_2
     ,[3] AS COMPONENT_INV_MAST_UID_3
FROM Assembly
PIVOT 
(
    MAX(COMPONENT_INV_MAST_UID)
    FOR sequence_number IN([1],[2],[3]) 
) as pvt

If you want to check Microsoft documentation. Pivot Table in Sql Server

Greetings

answered Sep 28 '18 at 15:18

Alvaro Parra

796
2
8
23

Wow! I didn't even know this existed! – Michael Viox Sep 28 '18 at 15:44

score 0 · Answer 2 · answered Sep 28 '18 at 15:11

Assuming you have max number of components of three you could use conditional aggregation like this. If you have a dynamic number of components we can still use conditional aggregation but need a few more hoops to accommodate for the dynamic number of columns.

declare @Assembly table
(
    inv_mast_uid int
    , sequence_number int
    , component_inv_mast_uid int
)

insert @Assembly values
(453061, 1, 453024)
, (453061, 2, 453017)
, (453061, 3, 453020)
, (453062, 1, 453019)
, (453062, 2, 453027)

select inv_mast_uid
    , component_inv_mast_uid_1 = max(case when sequence_number = 1 then component_inv_mast_uid end)
    , component_inv_mast_uid_2 = max(case when sequence_number = 2 then component_inv_mast_uid end)
    , component_inv_mast_uid_3 = max(case when sequence_number = 3 then component_inv_mast_uid end)
from @Assembly
group by inv_mast_uid
order by inv_mast_uid

Downvoter care to comment? This works perfectly given the data and requirements. In fact, conditional aggregation is slightly more performant than PIVOT and it is far less obtuse for syntax. — Sean Lange, Sep 28 '18 at 15:42
For anybody who might want to read about the differences Jeff Moden has a great article on the topic [here](http://www.sqlservercentral.com/articles/T-SQL/63681/). — Sean Lange, Sep 28 '18 at 15:53

score 0 · Answer 3 · answered Sep 28 '18 at 15:16

If max value of columns could be determined, something like this should work

select (select A.component_inv_mast_uid 
          from Assembly where inv_mast_uuid = A.inv_mast_uuid and sequence_number = 1) as component_inv_mast_uid_1,
       (select A.component_inv_mast_uid 
          from Assembly where inv_mast_uuid = A.inv_mast_uuid and sequence_number = 2) as component_inv_mast_uid_2,
       (select A.component_inv_mast_uid 
          from Assembly where inv_mast_uuid = A.inv_mast_uuid and sequence_number = 3) as component_inv_mast_uid_3
from (select distinct inv_mast_uid from Assembly) A

It also could be enhanced by dynamic request construction.

For Each Display on Same Row SQL Server

3 Answers3