Sample Header Ad - 728x90

How can I make a function similar to String_agg in Sql Server 2016?

0 votes
0 answers
10640 views
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 \ (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)
status
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](https://dbfiddle.uk/d9DIWVAT)
Asked by omerix (101 rep)
Oct 12, 2022, 08:05 PM
Last activity: Jan 4, 2025, 06:30 AM