October 2, 2020 at 5:34 pm
Hello
I´ve had this table u_parts
class|model|W20-13|w20-14|w20-15|
xrt |Model1|x |null | null
xrt |Model2|null | x | null
xrt |Model3|null |null | x
xrt |Model4|x |null | null
and convert it to this
class|model|type
xrt |Model1|w20-13
xrt |Model2|w20-14
xrt |Model3|w20-15
xrt |Model4|w20-13
October 2, 2020 at 5:36 pm
You've been here long enough to know this...
If you want a coded solution, please provide sample DDL and sample data in the form of INSERT statements.
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
October 2, 2020 at 7:08 pm
As Phil noted, I can't actually test the code, since you didn't provide any directly usable data:
SELECT up.class, up.model, ca1.type
FROM dbo.u_parts up
CROSS APPLY ( VALUES([W20-13]),([W20-14]),([WorkLog]) ) AS ca1(type)
WHERE ca1.type IS NOT NULL
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".
October 3, 2020 at 4:05 pm
See if this helps
select Class, Model, [Type]
from
(
select Class, Model,
CASE [W-20-13] WHEN 'x' THEN 'W-20-13' END as [W-20-13],
CASE [W-20-14] WHEN 'x' THEN 'W-20-14' END as [W-20-14],
CASE [W-20-15] WHEN 'x' THEN 'W-20-15' END as [W-20-15]
FROM u_parts
)up
UNPIVOT
(
[Type] For [Types] in ([W-20-13],[W-20-14],[W-20-15])
)
as unpvt;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply