May 9, 2018 at 7:41 am
You mean you want to create a delimited list? What have you tried so far?This is a very common question for a lot of RDBMS, including SQL Server. Consider having a search of "SQL Server Delimited list" on your favourite Internet Search Engine. As you're using 2014, you'll want to look for a solution using FOR XML PATH; you won't be able to make use of STRING_AGG, as that is only available in SQL Server 2017+. Don't look at answers that use a WHILE loop or a self referencing variable, they perform poorly.
If you get stuck, reply with the code you've tried and myself, or another user, will be more than happy to guide you in the right direction.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 9, 2018 at 8:49 am
Thom A - Wednesday, May 9, 2018 7:41 AMYou mean you want to create a delimited list? What have you tried so far?This is a very common question for a lot of RDBMS, including SQL Server. Consider having a search of "SQL Server Delimited list" on your favourite Internet Search Engine. As you're using 2014, you'll want to look for a solution using FOR XML PATH; you won't be able to make use of STRING_AGG, as that is only available in SQL Server 2017+. Don't look at answers that use a WHILE loop or a self referencing variable, they perform poorly.If you get stuck, reply with the code you've tried and myself, or another user, will be more than happy to guide you in the right direction.
I thought this is not a "delimited list". it is just like "Transpose" (convert vertical to horizontal) .
Pls help on this anyone....
May 9, 2018 at 9:00 am
SSC Venu - Wednesday, May 9, 2018 8:49 AMThom A - Wednesday, May 9, 2018 7:41 AMYou mean you want to create a delimited list? What have you tried so far?This is a very common question for a lot of RDBMS, including SQL Server. Consider having a search of "SQL Server Delimited list" on your favourite Internet Search Engine. As you're using 2014, you'll want to look for a solution using FOR XML PATH; you won't be able to make use of STRING_AGG, as that is only available in SQL Server 2017+. Don't look at answers that use a WHILE loop or a self referencing variable, they perform poorly.If you get stuck, reply with the code you've tried and myself, or another user, will be more than happy to guide you in the right direction.
I thought this is not a "delimited list". it is just like "Transpose" (convert vertical to horizontal) .
Pls help on this anyone....
You can't store multiple values in a variable, no; not unless it's a table variable. If you were using a table variable though then you'd need to declare it using dynamic SQL, as (I assume) you'll have variable amount of column.
You could dynamically transpose the data, however, I wouldn't recommend even attempting to try and store that in a variable.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 9, 2018 at 9:08 am
Are you saying that you have data stored in a column and you want to get all of the column's items into a string variable as a comma-delimited string?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 9, 2018 at 9:14 am
I feel like I've done the work for you now, however, maybe this'll show you what I mean.
CREATE TABLE #Sample ([Server] varchar(30));
INSERT INTO #Sample
VALUES ('SQL Server'),
('Oracle'),
('Sybase'),
('DB2'),
('Teradata'),
('Hana'),
('Hadoop');
GO
--Delimited List
DECLARE @a varchar(MAX);
SET @a = STUFF((SELECT ', ' + [Server]
FROM #Sample
FOR XML PATH('')),1,2,''); --Note, as there is no ORDER BY the order that the Servers will appear in random.
SELECT @a;
GO
DECLARE @sql nvarchar(MAX);
WITH CTE AS (
SELECT [Server],
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN --SELECT NULL, as there is no order in your table
FROM #Sample)
SELECT @sql =
N'SELECT ' +
STUFF((SELECT N',' + NCHAR(10) + N' ' + QUOTENAME([Server],N'''') + N' AS ' + QUOTENAME([Server])
FROM CTE
FOR XML PATH(N'')),1,9,N'') + N';';
PRINT @sql;
EXEC sp_executesql @sql;
GO
DROP TABLE #Sample;
Notice that the second dataset isn't stored in a variable; it's a dynamic SQL statement that is executed. You won't be able to (easily) get that data into a variable, as if you add another Server (for example MySQL), you need to create a different variable that has 8 columns in it, rather than 7. It's not a scalable solution.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 9, 2018 at 8:31 pm
Thom A - Wednesday, May 9, 2018 9:14 AMI feel like I've done the work for you now, however, maybe this'll show you what I mean.
CREATE TABLE #Sample ([Server] varchar(30));INSERT INTO #Sample
VALUES ('SQL Server'),
('Oracle'),
('Sybase'),
('DB2'),
('Teradata'),
('Hana'),
('Hadoop');
GO
--Delimited List
DECLARE @a varchar(MAX);SET @a = STUFF((SELECT ', ' + [Server]
FROM #Sample
FOR XML PATH('')),1,2,''); --Note, as there is no ORDER BY the order that the Servers will appear in random.
SELECT @a;
GODECLARE @sql nvarchar(MAX);
WITH CTE AS (
SELECT [Server],
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN --SELECT NULL, as there is no order in your table
FROM #Sample)
SELECT @sql =
N'SELECT ' +
STUFF((SELECT N',' + NCHAR(10) + N' ' + QUOTENAME([Server],N'''') + N' AS ' + QUOTENAME([Server])
FROM CTE
FOR XML PATH(N'')),1,9,N'') + N';';
PRINT @sql;
EXEC sp_executesql @sql;GO
DROP TABLE #Sample;
Notice that the second dataset isn't stored in a variable; it's a dynamic SQL statement that is executed. You won't be able to (easily) get that data into a variable, as if you add another Server (for example MySQL), you need to create a different variable that has 8 columns in it, rather than 7. It's not a scalable solution.
Thom A --- Thanks a lot. perfect solution.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply