February 23, 2019 at 3:52 pm
I'm trying to get the results of the top query (if null insert 0, comma between fields). Can get it done if I hard code field names but need it done dynamically. Thank you for any assistance!
IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test
CREATE TABLE #Test
(MyId int, p201812 int, p201811 int, p201810 int)
INSERT INTO #Test
SELECT 123, 10, 20, 30 UNION ALL
SELECT 456, 10, 0, 30 UNION ALL
SELECT 789, 11, 21, 31 UNION ALL
SELECT 987, 21, 31, CAST(NULL as int)
--this is my goal (but dynamically built)
--all fields concat except MyId, if null insert 0, comma between fields
SELECT MyId, CONCAT(ISNULL(p201812,0), ', ', ISNULL(p201811,0), ', ', ISNULL(p201810,0)) as MyConcat FROM #Test
--dynamically build
DECLARE @cols as nvarchar(MAX)
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(column_name)
FROM
(SELECT table_name, column_name, ordinal_position, data_type
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE table_name LIKE '#test%'
AND ordinal_position NOT IN (1)
) cols
ORDER BY ordinal_position
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'),1,1,'')
--SELECT @cols as ColString
DECLARE @sqlStr nvarchar(MAX)
SET @sqlStr = N' SELECT MyId, CONCAT(' + @cols + ') FROM #test'
--PRINT (@sqlStr)
EXEC (@sqlStr)
February 23, 2019 at 3:53 pm
So concat field will look lie this:
10, 20, 30
10, 0, 30
11, 21, 31
21, 31, 0
not like this:
102030
10030
112131
2131
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy