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;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply