sql - Transpose rows into columns in SQL Server, I try it using pivot but, expected result is not reached

Question

Transpose rows into columns in SQL Server, I try it using pivot but, expected result is not reached, using pivot getting Max value only

CREATE TABLE [dbo].[test]
(
    [patientid] [int] NULL,
    [sourcename] [varchar](200) NULL,
    [identifier] [varchar](100) NULL
) ON [PRIMARY]
GO

INSERT [dbo].[test] ([patientid], [sourcename], [identifier]) 
VALUES (100, N'SIN', N'2663563')
INSERT [dbo].[test] ([patientid], [sourcename], [identifier]) 
VALUES (100, N'SIN', N'2453433')
INSERT [dbo].[test] ([patientid], [sourcename], [identifier]) 
VALUES (100, N'MED', N'534545')
INSERT [dbo].[test] ([patientid], [sourcename], [identifier]) 
VALUES (100, N'MED', N'212334')
INSERT [dbo].[test] ([patientid], [sourcename], [identifier]) 
VALUES (100, N'NXG', N'8678')
INSERT [dbo].[test] ([patientid], [sourcename], [identifier]) 
VALUES (100, N'NXG', N'2131232334')

Expected output:

标签： sqlsql-serverpivotaggregate-functions

Answer 1

Use row_number() and conditional aggregation:

select patientid,
    max(case when sourcename = 'SIN' then identifier end) as sin,
    max(case when sourcename = 'MED' then identifier end) as med,
    max(case when sourcename = 'NXG' then identifier end) as nxg
from (
    select t.*, row_number() over(partition by sourcename, sourcename order by identifier) rn
    from test t
) t
group by patientid, rn