SQL Pivot Data in a table

Question

I am trying to create a new table with Pivot data.

Can some one point me in the right direction, or help me with a query to achieve the below please.

Answer 1

One way to do it is with a combination of CTEs and a final query with grouping: Let's first populate the data in a table:

CREATE TABLE COMPUTERS (CUSTOMER_ID INT, COMPUTER_NAME VARCHAR(50), COMPUTER_OS VARCHAR(50));
INSERT INTO COMPUTERS VALUES (15001, 'DESKTOP-JKVB','Windows 7');
INSERT INTO COMPUTERS VALUES (15001, 'DESKTOP-SKVB','Windows 2012R2');
INSERT INTO COMPUTERS VALUES (15002, 'PC-JKVB45','Windows VISTA');
INSERT INTO COMPUTERS VALUES (15002, 'JOHN-PC','Windows  10');
INSERT INTO COMPUTERS VALUES (15002, 'SERVER-DC','Windows 7');
INSERT INTO COMPUTERS VALUES (15002, 'DATA-PC','Windows 2016');
INSERT INTO COMPUTERS VALUES (15002, 'PC-BACKOFFICE','Windows 2008R2');
INSERT INTO COMPUTERS VALUES (15003, 'DESKTOP-XPBACK','Windows 7');
INSERT INTO COMPUTERS VALUES (15003, 'PC-HDFU','Windows  2012R2');
INSERT INTO COMPUTERS VALUES (15003, 'DESKTOP-NO2','Windows 10');
INSERT INTO COMPUTERS VALUES (15004, 'SERVER-DHCP','Windows 7');
INSERT INTO COMPUTERS VALUES (15004, 'DESKTOP-NO1','Windows 2012R2');

Here is the query. The only way thing is that you have to know what is the maximum number of records a user has (This is with 5 records) - dbfiddle:

;with cte as (
SELECT *,row_number() over (partition by Customer_ID order by Customer_ID) as Number
FROM computers
),
cte2 as (
select  DISTINCT
        c.Customer_ID,
        c.Computer_Name,
        'Computer_Name_' + cast(Number as varchar(1)) as Computer
from cte as c
),
cte_dist2 as (
select distinct 
        Customer_ID
from computers
),
cte3 as (
select c.Customer_ID,
        c.Computer_OS,
        'Computer_OS_' + cast(Number as varchar(1)) as OS
from cte as c
)
select  DISTINCT
        cd2.Customer_ID,
        MAX(IIF(c2.Computer='Computer_Name_1',c2.Computer_Name,NULL)) as Computer_Name_1,
        MAX(IIF(c3.OS='Computer_OS_1',c3.Computer_OS,NULL)) as Computer_OS_1,
        MAX(IIF(c2.Computer='Computer_Name_2',c2.Computer_Name,NULL)) as Computer_Name_2,
        MAX(IIF(c3.OS='Computer_OS_2',c3.Computer_OS,NULL)) as Computer_OS_2,
        MAX(IIF(c2.Computer='Computer_Name_3',c2.Computer_Name,NULL)) as Computer_Name_3,
        MAX(IIF(c3.OS='Computer_OS_3',c3.Computer_OS,NULL)) as Computer_OS_3,
        MAX(IIF(c2.Computer='Computer_Name_4',c2.Computer_Name,NULL)) as Computer_Name_4,
        MAX(IIF(c3.OS='Computer_OS_4',c3.Computer_OS,NULL)) as Computer_OS_4,
        MAX(IIF(c2.Computer='Computer_Name_5',c2.Computer_Name,NULL)) as Computer_Name_5,
        MAX(IIF(c3.OS='Computer_OS_5',c3.Computer_OS,NULL)) as Computer_OS_5
from cte_dist2 as cd2
    inner join cte2 as c2 ON cd2.Customer_ID = c2.Customer_ID
    inner join cte3 as c3 ON cd2.Customer_ID = c3.Customer_ID
group by cd2.Customer_ID