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".
Oct 18 2021 09:37:01
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows Server 2019 Standard 10.0 \ (Build 17763: ) (Hypervisor)
|
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
Last activity: Jan 4, 2025, 06:30 AM