asked on
Working with comma separated strings in a sql server table
Hi Experts,
I'm using the Employees table from the Northwind Traders sample database.
I'm only using these columns.
![Employees.PNG]()
If I use this query
I get the column names
![ColumnNames.PNG]()
If I use the STRING_AGG function query, i get this string:
![CurrentResult.PNG]()
What's an elegant way to get this result ?
I want to add the column name to the beginning of each part of the string like on this mockup image.
One way i was thinking, is before i even use the the STRING_AGG, in a select statement select all columns and add the column name to row 1 of each column,
Then once I added the column name to row 1 of every column than i could use STRING_AGG. But not sure if that is a good approach.
![StringListWithColumName.PNG]()
stringcolumns.PNG
I'm using the Employees table from the Northwind Traders sample database.
I'm only using these columns.
If I use this query
SELECT [name] AS ColNames
FROM syscolumns
WHERE id=OBJECT_ID('Employees')
AND [colid] < 4
ORDER BY [colid]
I get the column names
If I use the STRING_AGG function query, i get this string:
SELECT '(' + STRING_AGG([EmployeeID], ',') + '),'
+ '(' + STRING_AGG([LastName], ',') + '),'
+ '(' + STRING_AGG([FirstName], ',') + ')' AS [StringCol]
FROM [Northwind].[dbo].[Employees]
What's an elegant way to get this result ?
I want to add the column name to the beginning of each part of the string like on this mockup image.
One way i was thinking, is before i even use the the STRING_AGG, in a select statement select all columns and add the column name to row 1 of each column,
Then once I added the column name to row 1 of every column than i could use STRING_AGG. But not sure if that is a good approach.
stringcolumns.PNG
Microsoft SQL ServerSQL
Last Comment
Mark Wills
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for the info. It's very helpful to learn this syntax.
Could also use :
SELECT '(Employee_ID,' + STRING_AGG([EmployeeID], ',') + '),'
+ '(LastName,' + STRING_AGG([LastName], ',') + '),'
+ '(FirstName,' + STRING_AGG([FirstName], ',') + ')' AS [StringCol]
FROM [Northwind].[dbo].[Employees]
You have asked several related questions. What is your end goal for this data? You might look at a more commonly accepted data interchange method like JSON or XML instead of some hybrid CSV with parans, etc...
ASKER
slightwv,
I have a csv file that was exported from a process.
But the csv files contains only pieces of data i need. It's not a straight data dump. In the csv it only has parts of the data.
For example the csv file might contain only the vendor last name and purchase date for an order.
Then I would use that vendor last name and purchase data info from the csv file and look up the order details from an existing sql server table and from the query result set, transfer those order details data into a different existing table.
For some thing like that would you recommend using JSON or XML?
I have a csv file that was exported from a process.
But the csv files contains only pieces of data i need. It's not a straight data dump. In the csv it only has parts of the data.
For example the csv file might contain only the vendor last name and purchase date for an order.
Then I would use that vendor last name and purchase data info from the csv file and look up the order details from an existing sql server table and from the query result set, transfer those order details data into a different existing table.
For some thing like that would you recommend using JSON or XML?
So how is writing SQL that generates a CSV string like "(a,b,c),(d,e,f)" going to help get data into some destination table?
Seems like pretty straight forward ETL. Load the columns you want from the original CSV into a staging table.
Then it might be as simple: Insert into destination table ( select ...);
Seems like pretty straight forward ETL. Load the columns you want from the original CSV into a staging table.
Then it might be as simple: Insert into destination table ( select ...);
Sounds like there could be another new question happening pretty soon :)
Maybe with some sample data ? Edited of course so we can't see any data resembling "real life", more a symbolic representation of data..
Maybe with some sample data ? Edited of course so we can't see any data resembling "real life", more a symbolic representation of data..