Link to home
Start Free TrialLog in
Avatar of maqskywalker
maqskywalker

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.

User generated image


If I use this query

SELECT [name] AS ColNames 
FROM syscolumns 
WHERE id=OBJECT_ID('Employees')
AND [colid] < 4
ORDER BY [colid]

Open in new window


I get the column names

User generated image


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]

Open in new window


User generated image
 


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.



 User generated imagestringcolumns.PNG
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of maqskywalker
maqskywalker

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]

Open in new window

And should probably check for NULL so that they always align....
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...
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?
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 ...);
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..