How to use PIVOT on multiple columns without using temp table or views

Question

I am on Microsoft SQL Server 2016 and have 3 tables:

CREATE TABLE [dbo].[n](
    [ID] [int] NOT NULL,
    [IP] [nvarchar](50) NULL,
    [C] [nvarchar](255) NULL,
 CONSTRAINT [PK_N] PRIMARY KEY CLUSTERED 
( 
    [ID] ASC 
)
);

INSERT INTO n VALUES
(20005, '12.34.567.890', 'TEXT02'),
(20033, '90.87.654.32', 'TEXT01');

SELECT * FROM n;

CREATE TABLE [dbo].[v](
    [ID] [int] NOT NULL,
    [VID] [int] NOT NULL,
    [C] [nvarchar](75) NULL,
    [VT] [nvarchar](40) NULL,
    [VSU] [float] NULL,
    [VSA] [float] NULL,
    [VS] [float] NULL,
 CONSTRAINT [V_PK] PRIMARY KEY CLUSTERED 
(
    [ID] ASC,
    [VID] ASC
)
);

INSERT INTO v VALUES
(20005, 87748,  'Physical Memory',  'RAM', 7213740032,  9918472192, 17132212224),
(20005, 87749,  'Virtual Memory',   'Virtual Memory', 5319143424,   28943335424,    34262478848),
(20005, 87750,  'C:\ Label: 5C97F', 'Fixed Disk', 67721424896,  78335770624,    146057195520),
(20005, 87751,  'D:\ Label:Vo2 9C909',  'Fixed Disk', 13425840185344,   1574196776960,  15000036962304),
(20005, 87752,  'E:\ Label:Vi1 651E',   'Fixed Disk', 13427242958848,   1572793942016,  15000036900864),
(20033, 87885,  'Physical Memory',  'RAM', 2359943168,  10511417344,    12871360512),
(20033, 87886,  'Virtual Memory',   'Virtual Memory', 3684294656,   22056480768,    25740775424),
(20033, 87887,  'C:\ Label: 854E3', 'Fixed Disk', 69951520768,  229381271552,   299332792320),
(20033, 87888,  'D:\ Label:Vio EA629',  'Fixed Disk', 9679718285312,    12318431010816, 21998149296128);

SELECT * FROM v;

CREATE TABLE [dbo].[ncp](
    [ID] [int] NOT NULL,
    [MA] [nvarchar](400) NULL,
 CONSTRAINT [PK_NCP] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)
);

INSERT INTO ncp VALUES
(20005, 'APPNAME'),
(20033, 'APPNAME');

SELECT * FROM ncp;

What I am trying to get is for each n.C have a single row with all the details like v.C, v.VS, v.VSU and v.VSA

What I tried 1: I have written the part to get v.C and it works as expected. The SQL I came up for it:

SELECT * 
  FROM (
SELECT n.C AS [Device], 
       v.C AS [Volume],        
       'Volume' + cast(row_number() over (PARTITION BY n.C ORDER BY (select 1)) as varchar(4)) as dupVolume
  FROM n n
  JOIN v v
    ON v.ID = n.ID
  JOIN ncp ncp
    ON ncp.ID = n.ID
 WHERE ncp.MA LIKE '%APPNAME%'
   AND (v.VT LIKE 'fixed%' OR v.VT LIKE 'mount%' OR v.VT LIKE 'network%')
       ) T  
 PIVOT (
        MAX(Volume) FOR dupVolume IN ([Volume1],[Volume2],[Volume3])        
       ) P1;

Which gives result:

When I try to add v.VS, I get more than 2 rows.

What I tried 2: This is how I am trying to do it:

SELECT * 
  FROM (
SELECT n.C AS [Device], 
       v.C AS [Volume],
       v.VS AS [VolumeSize],
       'Volume' + cast(row_number() over (PARTITION BY n.C ORDER BY (select 1)) as varchar(4)) as dupVolume,
       'VolumeSize' + cast(row_number() over (PARTITION BY n.C ORDER BY (select 1)) as varchar(4)) as dupVolumeSize
  FROM n n
  JOIN v v
    ON v.ID = n.ID
  JOIN ncp ncp
    ON ncp.ID = n.ID
 WHERE ncp.MA LIKE '%APPNAME%'
   AND (v.VT LIKE 'fixed%' OR v.VT LIKE 'mount%' OR v.VT LIKE 'network%')
       ) T  
 PIVOT (
        MAX(Volume) FOR dupVolume IN ([Volume1],[Volume2],[Volume3])        
       ) P1
 PIVOT (
        MAX(VolumeSize) FOR dupVolumeSize IN ([VolumeSize1],[VolumeSize2],[VolumeSize3])
       ) P2;

But it pulls this:

Expected result: The result I want should be like:

This will give me v.C and v.VS. Later I want to get v.VSU and v.VSA as well. Three new columns will be added for each v.VSU (VSU1, VSU2, VSU3) and v.VSA (VSA1, VSA2, VSA3).

Question: How can I get the expected result? Do I even need pivot for this?

I am flexible to use any pure SQL approach as long as it gets me expected result but I can't use temp table or views as this SQL will be fed to a tool. That tool won't have access to create temp tables or views in the DB.

Answer 1

I think this is what you need.

SELECT *
INTO SRC
FROM (
SELECT N.C AS NC, V.C AS VC, ' VOLUME' + cast(row_number() OVER (
            PARTITION BY N.C ORDER BY V.C
            ) AS VARCHAR) AS COL
FROM V
INNER JOIN N
    ON (V.ID = N.ID)
WHERE V.VT = 'Fixed Disk'

UNION

SELECT N.C, CAST(CASE 
            WHEN VSU >= VSA
                AND VSU >= VS
                THEN VSU
            WHEN VSA >= VSU
                AND VSA >= VS
                THEN VSA
            WHEN VS >= VSU
                AND VS >= VSA
                THEN VS
            ELSE VSU
            END AS VARCHAR) AS vol, 'VOLUMESIZE' + cast(row_number() OVER (
            PARTITION BY N.C ORDER BY VSU
            ) AS VARCHAR)
FROM V
INNER JOIN N
    ON (V.ID = N.ID)
WHERE V.VT = 'Fixed Disk'
) A

and then after simple pivoting.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(COL) 
                    from SRC
                    GROUP BY COL 
                    order by 1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT NC AS DEVICE ,' + @cols + ' from 
             (
                select *
                from SRC
            ) x
            pivot 
            (
                max(VC)
                for COL in (' + @cols + ')
            ) p '

execute(@query);