Query to split data from rows into columns-CodePudding

I have a table that looks like this in SQL Server:

Client Name	Client ID Type	Client ID
John Dee	Netflix ID	2563
John Dee	Facebook ID	546543
Sara Spence	Netflix ID	7985
Sara Spence	Facebook ID	568867
Mark Brown	Netflix ID	8635
Mark Brown	Facebook ID	597489

I want to make query and show the data from row into column, expecting the result like this:

Client Name	Netflix ID	Facebook ID
John Dee	2563	546543
Sara Spence	7985	568867
Mark Brown	8635	597489

Is there any solution to show the table from row into column? I have many data in the table.

Version

Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64) Jan 12 2022 22:30:08 Copyright (C) 2019 Microsoft Corporation
Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

CodePudding user response：

Please try the following solution.

It follows the same minimal reproducible example paradigm. It is copied to SSMS as-is, executed, and you are getting the desired output.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ClientName VARCHAR(20), ClientIDType VARCHAR(20), ClientID INT);
INSERT @tbl (ClientName, ClientIDType, ClientID) VALUES
('John Dee', 'Netflix ID', 2563),
('John Dee', 'Facebook ID', 546543),
('Sara Spence', 'Netflix ID',   7985),
('Sara Spence', 'Facebook ID',  568867),
('Mark Brown', 'Netflix ID',    8635),
('Mark Brown', 'Facebook ID', 597489);
-- DDL and sample data population, end

SELECT ClientName, [Netflix ID] AS NetflixID, [Facebook ID] AS FacebookID
FROM 
   ( SELECT ClientName, ClientIDType, ClientID
     FROM @tbl 
   ) ps
PIVOT
   ( SUM (ClientID)
     FOR ClientIDType IN ( [Netflix ID], [Facebook ID])
   ) AS pvt;

Output

ClientName	NetflixID	FacebookID
John Dee	2563	546543
Mark Brown	8635	597489
Sara Spence	7985	568867

CodePudding user response：

SQL Server provides a PIVOT and UNPIVOT feature that should do what you are looking for.

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

CodePudding user response：

Just a JOIN would do in this case:

SELECT 
    t1.ClientName,
    t1.ClientID as NetflixID,
    t2.ClientID as FacebookID
FROM tbl t1
INNER JOIN tbl t2 on t1.ClientName=t2.ClientName and t2.ClientIDType='Facebook ID'
WHERE t1.ClientIdType='Netflix ID'

see: DBFIDDLE