CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Hexxed
Hexxed

Reputation: 683

Row result manipulation

I have an existing query that retrieves this data:

Key Type TextF
--- ---- ------
1   R    NULL
1   T    TEST
1   T    TEST2
2   R    NULL
2   T    FOO
3   R    NULL

Scenario:
Row type R will always have a NULL on TextF. However if the Key has a type T data existing, I should place the TextF on R data, joining them with CRLF or char(13)

Expected output based on given data:

Key Type TextF
--- ---- ----------
1   R    TEST TEST2
2   R    FOO
3   R    NULL

How can I achieve this through a query? I'm trying to make my existing query to be a subquery but I cant seem to make it work.

SELECT T0.*, *formatting here* FROM ( [myQuery] ) T0

Upvotes: 0

Views: 71

Answers (4)

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

In SQL Server 2017 you can use a new built-in function STRING_AGG

SELECT T0.[Key], T0.[Type],

(SELECT STRING_AGG (T1.TextF, CHAR(13)) AS TextF
   FROM [myTable] T1
  WHERE T1.[Type]='T' AND T1.[Key]=T0.[Key]
) TextF

FROM [myTable] T0
WHERE T0.[Type]='R'

Upvotes: 2

Pawan Kumar
Pawan Kumar

Reputation: 2021

Slightly different from other solutions--

DECLARE @MyTable TABLE ([Key] INT, Type VARCHAR(5), TextF VARCHAR(100))
INSERT INTO @MyTable VALUES
(1   ,'R', NULL),
(1   ,'T', 'TEST'),
(1   ,'T', 'TEST2'),
(2   ,'R', NULL),
(2   ,'T', 'FOO'),
(3   ,'R', NULL)

SELECT 
    T.[Key], 
    T.Type,
    STUFF 
                ((
                    SELECT '  ' + TextF
                    FROM @MyTable a
                    WHERE ( a.[Key] = T.[Key] )
                    FOR XML PATH('')
                ) ,1,2,'') 
                AS cusr
FROM @MyTable T
WHERE T.Type = 'R'

OUTPUT

Key         Type  cusr
----------- ----- --------------
1           R     TEST  TEST2
2           R     FOO
3           R     NULL

(3 rows affected)

Upvotes: 1

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use this.

DECLARE @MyTable TABLE ([Key] INT, Type VARCHAR(5), TextF VARCHAR(100))
INSERT INTO @MyTable VALUES
(1   ,'R', NULL),
(1   ,'T', 'TEST'),
(1   ,'T', 'TEST2'),
(2   ,'R', NULL),
(2   ,'T', 'FOO'),
(3   ,'R', NULL)

SELECT 
    T.[Key], 
    T.Type,
    CASE WHEN Type = 'R' THEN REPLACE(STUFF(X.TextF,1,1,''),'|', CHAR(13)) ELSE T.TextF END TextF 
FROM @MyTable T
        OUTER APPLY( SELECT '|' + TextF FROM @MyTable T1 
                        WHERE T.[Key] = T1.[Key] 
                            AND T1.Type <> 'R'
                            AND T1.TextF IS NOT NULL FOR XML PATH('')) X(TextF)
WHERE T.Type = 'R'

Result:

Key         Type  TextF
----------- ----- -------------
1           R     TEST
                  TEST2
2           R     FOO
3           R     NULL

Upvotes: 2

diiN__________
diiN__________

Reputation: 7656

I don't think it's the best solution but you could use the STUFF function to achieve your desired results:

SELECT t1.[Key],
    'R' [Type],
    STUFF((SELECT ' ' + t2.[TextF]
    FROM yourTable t2
    WHERE t2.[Key] = t1.[Key]
    FOR XML PATH('')), 1, 1, '') [TextF]
FROM yourTable t1
GROUP BY t1.[Key]

Upvotes: 2

Related Questions