May 23, 2019 at 6:47 pm
I have a table with the following data ( sample)
Col1 Col2 Col3 Col 4
A AA ABC ALL
B BA BBC A
C CA CCC ALL
I need the result as follows
Col1 Col2 Col3 Col 4
A AA ABC A
A AA ABC B
A AA ABC C
A AA ABC D
B BA BBC A
C CA CCC A
C CA CCC B
C CA CCC C
C CA CCC D
Basically where ever Col4 has ALL , the row needs to be repeated with A,B,C,D value in col4 . Any guidance would be greatly appreciated.
May 23, 2019 at 7:12 pm
This is one way.
DROP TABLE IF EXISTS #SomeTable;
CREATE TABLE #SomeTable
(
Col1 VARCHAR(5)
,Col2 VARCHAR(5)
,Col3 VARCHAR(5)
,Col4 VARCHAR(5)
);
INSERT #SomeTable
(
Col1
,Col2
,Col3
,Col4
)
VALUES
('A', 'AA', 'ABC', 'ALL')
,('B', 'BA', 'BBC', 'A');
DROP TABLE IF EXISTS #Mapping;
CREATE TABLE #Mapping
(
Col4 VARCHAR(5)
,NewCol4 VARCHAR(5)
);
INSERT #Mapping
(
Col4
,NewCol4
)
VALUES
('A', 'A')
,('B', 'B')
,('C', 'C')
,('D', 'D')
,('ALL', 'A')
,('ALL', 'B')
,('ALL', 'C')
,('ALL', 'D');
SELECT *
FROM #SomeTable;
SELECT st.Col1
,st.Col2
,st.Col3
,m.NewCol4
FROM #SomeTable st
JOIN #Mapping m
ON m.Col4 = st.Col4;
May 23, 2019 at 9:26 pm
SELECT ca1.*
FROM #SomeTable st
CROSS APPLY (
SELECT Col1, Col2, Col3, Col4
WHERE Col4 <> 'ALL'
UNION ALL
SELECT Col1, Col2, Col3, Col4
FROM ( VALUES('A'),('B'),('C'),('D') ) AS col4(col4)
WHERE st.Col4 = 'ALL'
) AS ca1
ORDER BY Col1, Col2, Col3, Col4
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 3 (of 3 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