CODEWITHSUNDEEP
t-sqlpivot
user3306489
user3306489

Reputation: 157

How to show rows as columns

I want to return data to show rows as columns for example;

ref, description
123, bananas, apples, oranges etc

There could be more than one description item.

The list from the table is shown in table rows.

ref, description
123, bananas
123, apples
123, oranges

Any ideas/code appreciated.

Heres the CTE I've created but am happy to explore other robust solutions.

with PivotV as (


SELECT        [CO-PERSON-VULNERABILITY].person_ref [Ref], 
(case 
when [CO_VULNERABLE-CODES].DESCRIPTION = 'Restricted Mobility' then 'RM'
when [CO_VULNERABLE-CODES].DESCRIPTION = 'Progressive or Long Term Illness' then 'PLTI'
when [CO_VULNERABLE-CODES].DESCRIPTION = 'ASB / Injunction Order' then 'AIO'
when [CO_VULNERABLE-CODES].DESCRIPTION = 'Beware possible Drug Paraphernalia' then 'BPDP'
when [CO_VULNERABLE-CODES].DESCRIPTION = '[Can''t] Manage Stairs' then 'CMS'


else NULL end) as [VunDesc]
--,[CO-PERSON-VULNERABILITY].vulnerable_code, [CO_VULNERABLE-CODES].[VULNERABLE-IND], 

FROM            [CO-PERSON-VULNERABILITY] INNER JOIN
                         [CO_VULNERABLE-CODES] ON [CO-PERSON-VULNERABILITY].vulnerable_code = [CO_VULNERABLE-CODES].[VULNERABLE-IND])

Upvotes: 0

Views: 50

Answers (1)

M. Kanarkowski
M. Kanarkowski

Reputation: 2195

Unfortunately, before SQL Server 2017(there you can use STRING_AGG()) it's not that easy to do what you want.

SELECT t1.ref
     , STUFF((
       SELECT ',' + t2.description
         FROM #t as t2
        WHERE t2.ref = t1.ref
        ORDER BY t2.description
          FOR XML PATH('')), 1, LEN(','), '') as description
  FROM #t as t1
 GROUP BY t1.ref

Upvotes: 1

Related Questions