CODEWITHSUNDEEP
sql-server
jinfo
jinfo

Reputation: 121

Order actual SQL data on a single new column

I got a few columns of data:

A1, A2, A3... AN

And every column can have common data with the others:

A1, A2, A3... AN
-------------------
P1, P4, P2, P3
P2, P3, P1, P3

And I want to get all that data in a single column ordered with a separator like:

A1, A2, A3... AN, ORDERED_DATA
---------------------------------
P1, P4, P2, P3, P1 | P2 | P3 | P4
P2, P3, P1, P3, P1 | P2 | P3 | P3

I need a SELECT query doing that. I don't know how ordering that before concat. Thanks.

Upvotes: 0

Views: 56

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 82020

Declare @Table table (id int,A1 varchar(10),A2 varchar(10),A3 varchar(10),A4 varchar(10))
Insert Into @Table values 
(1,'P1','P4','P2','P3'),
(2,'P2','P3','P1','P3')

;with cteNorm as (
    Select id,Val = A1 From @Table
    Union All
    Select id,Val = A2 From @Table
    Union All
    Select id,Val = A3 From @Table
    Union All
    Select id,Val = A4 From @Table
)
Select id, Ordered = STUFF((
    SELECT ' | ' + val FROM cteNorm
    WHERE id = x.id
    Order by id,Val
    FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')
FROM cteNorm AS x
GROUP BY id

Returns

id  Ordered
1   P1 | P2 | P3 | P4
2   P1 | P2 | P3 | P3

Upvotes: 0

Chris Pickford
Chris Pickford

Reputation: 9001

Another method in addition to the other answers, using UNPIVOT and STUFF/FOR XML:

SELECT  t.ID,
        t.A1,
        t.A2,
        t.A3,
        t.A4,
        STUFF((
               SELECT   ' | ' + unpvt.Val
               FROM     @table
               UNPIVOT ( Val FOR Col IN (A1, A2, A3, A4) ) unpvt
               WHERE    unpvt.ID = t.ID
               ORDER BY unpvt.Val
               FOR XML PATH('')
              ), 1, 3, '') AS ORDERED_DATA
FROM    @table AS t;

Working example

ID  A1  A2  A3  A4  ORDERED_DATA
1   P1  P4  P2  P3  P1 | P2 | P3 | P4
2   P2  P3  P1  P3  P1 | P2 | P3 | P3

Upvotes: 0

JamieD77
JamieD77

Reputation: 13949

you can probably do something like this using FOR XML to concatenate the values

SELECT  A1, A2, A3, A4, 
        STUFF(ca.data, 1, 3, '') AS Ordered_Data 
FROM    myTable
        OUTER APPLY (SELECT ' | ' + t.v
                     FROM ( VALUES(A1), (A2), (A3), (A4) ) t(v)
                     ORDER BY t.v
                     FOR XML PATH('')
                    ) ca(data)

if you have null values, you probably want to use WHERE t.v IS NOT NULL in the outer apply query

Upvotes: 1

Related Questions