Microsoft SQL Server
--
Questions
--
Followers
Top Experts
I have a query which is working with STRING_AGG but the deployment server has 2013 version so I would like to convert the piece of code to support that version how can I do that
DECLARE @StageOfPassage TABLE
(
OperationOfVessel NVARCHAR(MAX),
ReportableEventId INT
)
INSERT INTO @StageOfPassage
SELECT STRING_AGG(c.LookupDataName, ','),
s.ReportableEventId
FROM dbo.VesselDetail AS s
CROSS APPLY STRING_SPLIT(s.OperationofVesselId, ',') AS f
INNER JOIN dbo.LookupData AS c
ON f.value = c.LookupDataId
GROUP BY s.ReportableEventId;
SELECT * FROM @StageOfPassageZero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
There are MANY examples of this out there.
For example:
https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/
Hi slightwv (䄆 Netminder) the problem I had is in one table the Id's are stored as comma separated values so I am a bit confused do apply those
Here is what I will have a master table and in the child table it stores the comma separated value as follows
Table1
Vid RId CommaId
1 1 1,2
2 2 5,7
Table2
ColId ColName
1 Test
2 Test1
3 Test2
4 Test3
5 Test4
6 Test5
7 Test6
I need to join these two so that the output will be like
ColName RId
Test,Test1 1
Test4,Test6 2
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
It only causes problems like in your last questions and doesn't follow the rules of normalization.
It also does things like prevent proper indexing and such.
It is just a bad design.
That said, try this:
select
rid,
stuff(csv,1,1,'')
from table1 t1
cross apply (
select ',' + colname
from table2 t2
where charindex(',' + cast(t2.colid as varchar(10)) + ',', ',' + t1.commaid + ',') > 0
order by colid
for xml path('')
) x(csv)
group by rid, csv
;Complete working fiddle:
https://dbfiddle.uk/6iP2Qcuu
Thank you is it possible to exclude NULL values?
If you cannot figure it out, can you update your test data and expected results to show where nulls are involved?
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
It will be the same like this
Vid RId CommaId
1 1 1,2
2 2 5,7
3 3 NULL
4 4 NULL
5 5 2,5
It should result only 1, 2 and 5
Microsoft SQL Server
--
Questions
--
Followers
Top Experts
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.