December 14, 2016 at 5:12 am
I have a table like this
Col1Col2
Adam1
Barbara, Catherina1
Barbara, Catherina2
Adam, Catherina1
Adam, Catherina2
Barbara, Adam, Daniela1
Barbara, Adam, Daniela2
Barbara, Adam, Daniela3
Need a select statement to populate Col3 like shown below
Col1Col2Col 3
Adam1Adam
Barbara, Catherina1Barbara
Barbara, Catherina2Catherina
Adam, Catherina1Adam
Adam, Catherina2Catherina
Barbara, Adam, Daniela1Barbara
Barbara, Adam, Daniela2Adam
Barbara, Adam, Daniela3Daniela
I tried using SUBSTRING & CHARINDEX but was not able to fid the solution. Need a SQL where we can pass Col2 value and populate Col3.
Uploaded the sample table as attachment.
Please help.
Thank you, Vikram
December 14, 2016 at 5:23 am
Vikram
If Col1 is always in the format shown, then you can indeed use SUBSTRING, something like this:
SUBSTRING(Col1,Col2*2-1,1)
John
December 14, 2016 at 5:38 am
Hi John, thanks for the quick reply but I was not clear earlier. Can you check my post again now.
Thank you, Vikram
December 14, 2016 at 5:45 am
vikram_patro (12/14/2016)
Hi John, thanks for the quick reply but I was not clear earlier. Can you check my post again now.Thank you, Vikram
Are you able to provide your sample data and desired results in the form of SELECT statements, please? It's not clear from your existing posts where the columns begin and end.
December 14, 2016 at 5:49 am
I attached the sample data in excel to the original post, hope that helps.
Also attaching here.
December 14, 2016 at 5:51 am
vikram_patro (12/14/2016)
I attached the sample data in excel to the original post, hope that helps.Also attaching here.
I mean, like this
SELECT *
FROM
(
VALUES
('Adam', 1)
, ('Barbara, Catherina', 1)
) x (Col1, Col2);
Doing it this way means that others can cut/paste straight into SSMS and run it for themselves.
December 14, 2016 at 7:26 am
How about using a well-known highly performant string splitter function, that you can find here:
http://www.sqlservercentral.com/articles/Tally+Table/72993/%5B/url%5D
SELECT Col1, Col2, S.Item AS Col3
FROM YourTable AS YT
CROSS APPLY dbo.DelimitedSplit8K(YT.Col1, ',') AS S
WHERE YT.Col2 = S.ItemNumber;
There's a lot to be learned from the article referenced above.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 14, 2016 at 7:34 am
This will give you an idea of how to do it. I'm sure you'll be able to make it a lot simpler than I have!CREATE TABLE #Names (Col1 varchar(4000), Col2 int)
INSERT INTO #Names
VALUES
('Adam', 1)
,('Barbara, Catherina', 1)
,('Barbara, Catherina', 2)
,('Adam, Catherina', 1)
,('Adam, Catherina', 2)
,('Barbara, Adam, Daniela', 1)
,('Barbara, Adam, Daniela', 2)
,('Barbara, Adam, Daniela', 3)
WITH N8 AS ( -- Generate 8 rows
SELECT Nbr FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8)) v(Nbr)
)
, N64 AS ( -- Generate 64 rows
SELECT a.Nbr
FROM N8 a CROSS JOIN N8
)
, N4096 AS ( -- Generate 4096 rows
SELECT a.Nbr
FROM N64 a CROSS JOIN N64
)
, Numbers AS ( -- Number the rows sequentially
SELECT ROW_NUMBER() OVER (ORDER BY Nbr) AS Nbr
FROM N4096
)
, Splitted AS ( -- Split Col1 into individual letters - one letter on each row
SELECT
t.Col1
,t.Col2
,SUBSTRING(',' + t.Col1 + ',',n.Nbr,1) NthChar
,n.nbr AS Position
FROM #Names t
JOIN Numbers n ON n.Nbr <= LEN(Col1) + 2
) --select * from splitted
, Positions AS ( -- Choose just the commas and number each occurrence thereof
SELECT
Col1
,Col2
,Position
,ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Position) AS Occurrence
FROM Splitted
WHERE NthChar = ','
)
, Names AS ( -- get the name between (n-1)th and nth occurrences and remove commas and spaces
SELECT
REPLACE(REPLACE(
SUBSTRING(
Col1
,Position-1
,LEAD(Position) OVER (PARTITION BY Col1,Col2 ORDER BY Occurrence ) - Position
),' ',''),',','') AS Col3
,Col1
,Col2
FROM positions
WHERE occurrence IN (Col2,Col2+1)
)
SELECT Col1, Col2, Col3
FROM Names
WHERE Col3 IS NOT NULL
Edit - Steve shows above how much simpler you can make it!
John
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply