0

I know that I will use stuff and for xml for this process, but in this way, the sentence like the one below becomes too long.

Unfortunately I don't actually have a table like "controlTable".

I actually write a long sentence to generate the controlTable and connect it to my main sql statement with "outer apply".

select @@version;
(No column name)
Microsoft SQL Server 2016 (SP3-OD) (KB5006943) - 13.0.6404.1 (X64)
Oct 18 2021 09:37:01
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
CREATE TABLE [dbo].[controlTable](
    [category] [nvarchar](50) NULL,
    [control1] [int] NULL,
    [control2] [int] NULL,
    [control3] [int] NULL,
    [control4] [int] NULL,
    [control5] [int] NULL,
    [control6] [int] NULL
) ON [PRIMARY]
INSERT [dbo].[controlTable] ([category], [control1], [control2], [control3], [control4], [control5], [control6]) VALUES (N'Accessories', 19, 0, 12, 0, 0, 7)
INSERT [dbo].[controlTable] ([category], [control1], [control2], [control3], [control4], [control5], [control6]) VALUES (N'Pocket', 6, 0, 5, 0, 0, 1)
INSERT [dbo].[controlTable] ([category], [control1], [control2], [control3], [control4], [control5], [control6]) VALUES (N'Button', 28, 0, 27, 0, 1, 0)
INSERT [dbo].[controlTable] ([category], [control1], [control2], [control3], [control4], [control5], [control6]) VALUES (N'Women', 25, 0, 24, 0, 0, 1)
INSERT [dbo].[controlTable] ([category], [control1], [control2], [control3], [control4], [control5], [control6]) VALUES (N'Men', 15, 0, 11, 0, 2, 2)
5 rows affected
select * from controlTable
category control1 control2 control3 control4 control5 control6
Accessories 19 0 12 0 0 7
Pocket 6 0 5 0 0 1
Button 28 0 27 0 1 0
Women 25 0 24 0 0 1
Men 15 0 11 0 2 2
/*
select 
STRING_AGG(category,'|')  as 'category'
,STRING_AGG(control1,'|')  as 'control1'
,STRING_AGG(control2,'|') as 'control2'
,STRING_AGG(control3,'|') as 'control3'
,STRING_AGG(control4,'|') as 'control4'
,STRING_AGG(control5,'|') as 'control5'
,STRING_AGG(control6,'|') as 'control6'
from controlTable

Accessories|Pocket|Button|Women|Men 19|6|28|25|15 0|0|0|0|0 12|5|27|24|11 0|0|0|0|0 0|0|1|0|2 7|1|0|1|2 */

select 

(
select 
STUFF((select '|' + rtrim(replace(ct.category,'|','')) 
from controlTable ct
for xml path('')),1,1,'') as 'category'
) as 'category'

,(
select 
STUFF((select '|' + rtrim(replace(ct.control1,'|','')) 
from controlTable ct
for xml path('')),1,1,'') as 'category'
) as 'control1'


,(
select 
STUFF((select '|' + rtrim(replace(ct.control2,'|','')) 
from controlTable ct
for xml path('')),1,1,'') as 'category'
) as 'control2'


,(
select 
STUFF((select '|' + rtrim(replace(ct.control3,'|','')) 
from controlTable ct
for xml path('')),1,1,'') as 'category'
) as 'control3'



,(
select 
STUFF((select '|' + rtrim(replace(ct.control4,'|','')) 
from controlTable ct
for xml path('')),1,1,'') as 'category'
) as 'control4'



,(
select 
STUFF((select '|' + rtrim(replace(ct.control5,'|','')) 
from controlTable ct
for xml path('')),1,1,'') as 'category'
) as 'control5'



,(
select 
STUFF((select '|' + rtrim(replace(ct.control6,'|','')) 
from controlTable ct
for xml path('')),1,1,'') as 'category'
) as 'control6'
category control1 control2 control3 control4 control5 control6
Accessories|Pocket|Button|Women|Men 19|6|28|25|15 0|0|0|0|0 12|5|27|24|11 0|0|0|0|0 0|0|1|0|2 7|1|0|1|2

fiddle

omerix
  • 101
  • 1
  • 1
  • 7

0 Answers0