How to insert sql table item list into one row in table
-
I have a select statement that returns an item list from a split function.
.................................................................................
DECLARE @testString varchar(3000)
SET @testString = ('string1~string2~string3~string4~string5~string6~string7');
SELECT item FROM dob_DelimitedSplit8K(@testString, '~')
item
-----
string1
string2
string3
string4
string5
string6
string7
.................................................................................
I want to insert the item list into another table in one row
.................................................................................
id s1 s2 s3 s4 s5 s6 s7
-- --- --- --- --- --- --- ---
01 string1 string2 string3 string4 string5 string6 string7
Also - i have fifteen columns in table 2, so it should just insert null if there isn't a value to put in the column
I am sure this should not be so hard, I am just having trouble to figure it out.
Thanks
-
You might want to take a look at cross tabs. It's fairly easy and it just requires copy-paste-replace. 🙂
http://www.sqlservercentral.com/articles/T-SQL/63681/
SELECT
MAX(CASE WHEN ItemNumber = 1 THEN Item END) AS s1,
MAX(CASE WHEN ItemNumber = 2 THEN Item END) AS s2,
MAX(CASE WHEN ItemNumber = 3 THEN Item END) AS s3,
MAX(CASE WHEN ItemNumber = 4 THEN Item END) AS s4,
MAX(CASE WHEN ItemNumber = 5 THEN Item END) AS s5,
MAX(CASE WHEN ItemNumber = 6 THEN Item END) AS s6,
MAX(CASE WHEN ItemNumber = 7 THEN Item END) AS s7,
MAX(CASE WHEN ItemNumber = 8 THEN Item END) AS s8,
MAX(CASE WHEN ItemNumber = 9 THEN Item END) AS s9,
MAX(CASE WHEN ItemNumber = 10 THEN Item END) AS s10,
MAX(CASE WHEN ItemNumber = 11 THEN Item END) AS s11,
MAX(CASE WHEN ItemNumber = 12 THEN Item END) AS s12,
MAX(CASE WHEN ItemNumber = 13 THEN Item END) AS s13,
MAX(CASE WHEN ItemNumber = 14 THEN Item END) AS s14,
MAX(CASE WHEN ItemNumber = 15 THEN Item END) AS s15
FROM dbo.DelimitedSplit8K(@testString, '~')
-
Worked perfect for me, Thanks for the quick answer.
I am going to build it into a function and then I will be able to call it and fill the table on the fly.
Thanks again
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply