;WITH group1 AS
(
SELECT [CLASS] AS EXTENSION, [CLASSNAME] AS TYPE, [KEYNAME] AS KIND, [KEY]
FROM SYSTEM WHERE KEYNAME = '#APPLICATION'
)
Second query collects "VBOX" name, group2 AS
(
SELECT [CLASS] AS EXTENSION, [CLASSNAME] AS TYPE, [KEYNAME] AS KIND, [KEY]
FROM SYSTEM WHERE KEYNAME = '#VBOX'
)
Third uses the two group to present a result with single row per record.SELECT * FROM group1
JOIN group2 on group1.EXTENSION = group2.EXTENSION
ORDER BY group1.EXTENSION ASC
These work well but my issue is the columns present "numeric" values for the "KEY" columns. So I need to re-query the "SYSTEM" table to find matching data that match the [KEY].numeric result.EXTENSION TYPE KIND KEY EXTENSION TYPE KIND KEY
11011121600 VMX #APPLICATION AF71B74D301842C 11011121600 VMX #VBOX BC56379D-B99C-4058-A563-E0CE3D0C1480
The above is a sample of the result. Want to query the "SYSTEM" table looking for more data regarding each row's final KEY column. Example looking for "BC56379D-B99C-4058-A563-E0CE3D0C1480". The result will be two column with EXTENSION and the result of the forth query. CLASS CLASSNAME KEYNAME KEY
------------- ----------- -------------- --------------------------------------
11011121600 VMX #APPLICATION AF71B74D301842C
11011121600 VMX #VBOX BC56379D-B99C-4058-A563-E0CE3D0C1480
A1011121600 VMX #APPLICATION AF71B74D301842C
B1011121600 VMX #VBOX BC56379D-B99C-4058-A563-E0CE3D0C1480
11011121600 VMX #xyz AF71B74D301842C
then:CREATE TABLE system(
CLASS VARCHAR(11) NOT NULL
,CLASSNAME VARCHAR(3) NOT NULL
,KEYNAME VARCHAR(12) NOT NULL
,[KEY] VARCHAR(36) NOT NULL
);
INSERT INTO system(CLASS,CLASSNAME,KEYNAME,[KEY]) VALUES ('11011121600','VMX','#APPLICATION','AF71B74D301842C');
INSERT INTO system(CLASS,CLASSNAME,KEYNAME,[KEY]) VALUES ('11011121600','VMX','#VBOX','BC56379D-B99C-4058-A563-E0CE3D0C1480');
INSERT INTO system(CLASS,CLASSNAME,KEYNAME,[KEY]) VALUES ('A1011121600','VMX','#APPLICATION','AF71B74D301842C');
INSERT INTO system(CLASS,CLASSNAME,KEYNAME,[KEY]) VALUES ('B1011121600','VMX','#VBOX','BC56379D-B99C-4058-A563-E0CE3D0C1480');
INSERT INTO system(CLASS,CLASSNAME,KEYNAME,[KEY]) VALUES ('11011121600','VMX','#xyz','AF71B74D301842C');
and the query I provided earlier:select
[CLASS] AS EXTENSION
, max(case when KEYNAME = '#APPLICATION' then [CLASS] end) as Application
, max(case when KEYNAME = '#APPLICATION' then [CLASSNAME] end) as ApplicationType
, max(case when KEYNAME = '#APPLICATION' then [KEY] end) as ApplicationKey
, max(case when KEYNAME = '#VBOX' then [CLASS] end) as Vbox
, max(case when KEYNAME = '#VBOX' then [CLASSNAME] end) as VboxType
, max(case when KEYNAME = '#VBOX' then [KEY] end) as VboxKey
, max(case when KEYNAME = '#xyz' then [CLASS] end) as xyz
, max(case when KEYNAME = '#xyz' then [CLASSNAME] end) as xyzType
, max(case when KEYNAME = '#xyz' then [KEY] end) as xyzKey
from [system]
where [keyname] in ('#APPLICATION','#VBOX','#xyz')
group by
[CLASS]
will produce this result: EXTENSION Application ApplicationType ApplicationKey Vbox VboxType VboxKey xyz xyzType xyzKey
------------- ------------- ----------------- ----------------- ------------- ---------- -------------------------------------- ------------- --------- -----------------
11011121600 11011121600 VMX AF71B74D301842C 11011121600 VMX BC56379D-B99C-4058-A563-E0CE3D0C1480 11011121600 VMX AF71B74D301842C
A1011121600 A1011121600 VMX AF71B74D301842C
B1011121600 B1011121600 VMX BC56379D-B99C-4058-A563-E0CE3D0C1480
Notice I have deliberately included data that does not fill out every cell in the result, but IF you have 3 original rows that share the same [Class] and those 3 rows have [keyname] in ('#APPLICATION','#VBOX','#xyz') THEN the query will combine those 3 rows into one row of output.| CATEGORIES | CLASSNAME | CLASS | BLINDTRANSFER | FORWARDKEY | INTERRUPTIBLE | LAST_UPDATE | MAXPLAYTIME | NAME | PBXSWITCHING | PLAYLASTPOS | QTY_INVALIDKEYS | QTY_NOINPUT | RANDOMPLAYLIST | STATISTIC | TIMEOUT | TYPE | VAR_RESULT |
|-------------|-----------------|--------------------------------------|---------------|------------|---------------|--------------------|-------------|-------------------------|--------------|-------------|-----------------|-------------|----------------|-----------|---------|------|------------|
| $VOICEBOXES | 235FAAEF975046E | 54EB80BD-1DF9-41CC-92D2-71F4E4139BC9 | | | X | 23/4/2021 13:11:55 | 0 | CLERKS Main menu | | _ | 3 | 3 | _ | X | 5 | MENU | |
| $VOICEBOXES | 246856681A1B41D | 48E98324-A3ED-4B64-B721-AB22F12E8EED | | | | 23/4/2021 13:41:33 | | ENGINEERING NEW MENU | | _ | 2 | 2 | _ | X | 5 | MENU | |
| $VOICEBOXES | 246856681A1B41D | B467D086-86EB-438E-9D58-814DA9783508 | | | X | 23/4/2021 13:41:33 | 0 | BDRS MAIN MENU | | _ | 3 | 3 | _ | X | 2 | MENU | |
| $VOICEBOXES | 8DC0C20DFC194CD | 8AEBAC98-477B-48A6-B92E-97E3CCCFE494 | | | X | 24/7/2019 10:37:1 | 0 | HOLIDAY Closed Greeting | | _ | | | _ | X | | PLAY | |
The query I used to produce this involves some "dynamic sql" (sql that produces sql code and then executes it) which is needed to get the keynames as column names:DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(s.keyname)
FROM system s
WHERE NOT keyname LIKE '#%'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query = 'SELECT CATEGORIES,CLASSNAME,CLASS, ' + @cols + ' FROM
(
SELECT
s.CATEGORIES,s.CLASSNAME,s.CLASS,s.KEYNAME,s.[KEY]
FROM system s
inner join (
select
class as grouping
, max(case when keyname = ''#APPLICATION'' then [key] end) as gclassname
, max(case when keyname = ''#VBOX'' then [key] end) as gclass
from system
where keyname in (''#APPLICATION'',''#VBOX'')
group by
class
) g on s.classname = g.gclassname and s.class = g.gclass
) sourcedata
pivot
(
max([key])
FOR [keyname] IN (' + @cols + ')
) p '
--select @query -- use select to inspect the generated sql
execute(@query) -- once satisfied that sql is OK, use execute
note at the bottom 2 rows, you can "select" the generated SQL to inspect it (which I do to debug before executing)SELECT CATEGORIES,CLASSNAME,CLASS, [BLINDTRANSFER],[FORWARDKEY],[INTERRUPTIBLE],[LAST_UPDATE],[MAXPLAYTIME],[NAME],[PBXSWITCHING],[PLAYLASTPOS],[QTY_INVALIDKEYS],[QTY_NOINPUT],[RANDOMPLAYLIST],[STATISTIC],[TIMEOUT],[TYPE],[VAR_RESULT] FROM
(
SELECT
s.CATEGORIES,s.CLASSNAME,s.CLASS,s.KEYNAME,s.[KEY]
FROM system s
inner join (
select
class as grouping
, max(case when keyname = '#APPLICATION' then [key] end) as gclassname
, max(case when keyname = '#VBOX' then [key] end) as gclass
from system
where keyname in ('#APPLICATION','#VBOX')
group by
class
) g on s.classname = g.gclassname and s.class = g.gclass
) sourcedata
pivot
(
max([key])
FOR [keyname] IN ([BLINDTRANSFER],[FORWARDKEY],[INTERRUPTIBLE],[LAST_UPDATE],[MAXPLAYTIME],[NAME],[PBXSWITCHING],[PLAYLASTPOS],[QTY_INVALIDKEYS],[QTY_NOINPUT],[RANDOMPLAYLIST],[STATISTIC],[TIMEOUT],[TYPE],[VAR_RESULT])
) p
all this may be viewed here at dbfiddle.uk
That being said a more conventional way to achieve what I think you want would be to use "conditional aggregates" like this
Open in new window
Note: With this approach is you will get rows even if there are classes without both '#APPLICATION' and '#VBOX'.i.e. a row will display if there is an application without a matching vbox or vice versa.
You can repeat the column clusters for each extra value found in [KEYNAME]
e.g. if there is also a value to be found in [KEYNAME] that is '#xyz', then:
Open in new window