疑难SQL语句

[evanhb]

create table #tb(A1 varchar(20), B1 VARCHAR(10) , C1 VARCHAR(10),D1 VARCHAR(10))

insert into #tb values('Fitemid','3002','2980','75C')
insert into #tb values('5504014','0.48','0.25','0.01')
insert into #tb values('5504018','0.78','0.22','0.02')
insert into #tb values('7704021','1.25','0.5','0.08')

源数据 3002\2980\75C是子项
产品名称 3002 2980 75C
5504014 0.48 0.25 0.01
5504018 0.78 0.22 0.02
7704021 1.25 0.5 0.08


需要结果如下
产品名称 子项名称 用量
5504014 3002 0.48
5504014 2980 0.25
5504014 75C 0.01
5504018 3002 0.78
5504018 2980 0.22
5504018 75C 0.02
7704021 3002 1.25
7704021 2980 0.5
7704021 75C 0.08


请各位专家指教，谢谢

[RINK_1]

WITH CTE1
AS
(select B1,'B1' as item_type from table
where A1='Fitemid'
UNION ALL
select C1,'C1' from table
where A1='Fitemid'
UNION ALL
select D1,'D1' from table
where A1='Fitemid'),

CTE2
AS
(select A1,B1,'B1' as item_type from table
where A1<>'Fitemid'
UNION ALL
select A1,C1,'C1' from table
where A1<>'Fitemid'
UNION ALL
select A1,D1,'D1' from table
where A1<>'Fitemid')

SELECT A.A1,B.B1,A.B1 FROM CTE2 A
JOIN CTE1 B ON A.item_type=B.item_type
order by A.A1

[wwfxgm]

这数据结构好奇怪。第一行和其他行还不是同一个属性。 有这样的设计的数据库结构吗？

[二月十六]

create table #tb(A1 varchar(20), B1 VARCHAR(10) , C1 VARCHAR(10),D1 VARCHAR(10))
 
insert into #tb values('Fitemid','3002','2980','75C')
insert into #tb values('5504014','0.48','0.25','0.01')
insert into #tb values('5504018','0.78','0.22','0.02')
insert into #tb values('7704021','1.25','0.5','0.08')

;WITH cte AS (
SELECT  A1 AS 产品名称 ,
        ( SELECT TOP 1
                    B1
          FROM      #tb a
          WHERE     A1 = 'Fitemid'
        ) AS 子项名称 ,
        B1 AS 用量
FROM    #tb
WHERE   A1 <> 'Fitemid'
UNION
SELECT  A1 ,
        ( SELECT TOP 1
                    C1
          FROM      #tb a
          WHERE     A1 = 'Fitemid'
        ) AS 子项名称 ,
        C1
FROM    #tb
WHERE   A1 <> 'Fitemid'
UNION
SELECT  A1 ,
        ( SELECT TOP 1
                    D1
          FROM      #tb a
          WHERE     A1 = 'Fitemid'
        ) AS 子项名称 ,
        D1
FROM    #tb
WHERE   A1 <> 'Fitemid'
)
SELECT * FROM cte
DROP TABLE #tb

[OwenZeng_DBA]

引用 5 楼 yisuylm 的回复:

虽然版主给出了答案，但是我觉得还是改数据库结构是上策，版主这样做害人一生

说得太严重了，，有的新人将只想知道当前的答案，不会想那么多

[shoppo0505]

with main_data as ( select *, _index = row_number()over(order by A1) from #tb where A1<>'Fitemid' ) , temp1 as ( select main_data._index*3-2 as _index, A1 as ID, B1 as value1 from main_data union all select main_data._index*3-1, A1,C1 from main_data union all select main_data._index*3, A1, D1 from main_data ) , temp2 as ( select 1 as _index, B1 from #tb where A1='Fitemid' UNION ALL select 2 as _index, C1 from #tb where A1='Fitemid' UNION ALL select 3 as _index, D1 from #tb where A1='Fitemid' ) select temp1.ID, B1, value1 from temp1 inner join temp2 on temp2._index = ((temp1._index-1)%3+1) order by id, value1 desc 语句是给了，但是说实话，这种结构太奇怪了，不知道意义何在。 现在的语句也只是解决当前问题，问题根本是结构不对

[吉普赛的歌]

IF OBJECT_ID('tempdb..#tb') IS NOT NULL DROP TABLE #tb
create table #tb(A1 varchar(20), B1 VARCHAR(10) , C1 VARCHAR(10),D1 VARCHAR(10))
insert into #tb values('Fitemid','3002','2980','75C')
insert into #tb values('5504014','0.48','0.25','0.01')
insert into #tb values('5504018','0.78','0.22','0.02')
insert into #tb values('7704021','1.25','0.5','0.08')

;WITH cte(Item) AS (
	SELECT B1 FROM #tb WHERE A1='Fitemid'
	UNION ALL
	SELECT C1 FROM #tb WHERE A1='Fitemid'
	UNION ALL
	SELECT D1 FROM #tb WHERE A1='Fitemid'	
)
,cte2 AS (
SELECT ROW_NUMBER() OVER (PARTITION BY a.A1 ORDER BY a.B1) AS rid,a.A1,b.Item,a.B1 
FROM #tb AS a CROSS APPLY cte AS b
WHERE a.A1!='Fitemid'
)
SELECT
A1 AS [产品名称]
,[Item] AS [子项名称]
,(SELECT CASE rid WHEN 1 THEN B1 WHEN 2 THEN C1 ELSE D1 END FROM #tb AS b WHERE b.A1=a.A1) AS [用量] 
FROM cte2 a  ORDER BY a.a1
/*
产品名称             子项名称   用量
-------------------- ---------- ----------
5504014              3002       0.48
5504014              2980       0.25
5504014              75C        0.01
5504018              75C        0.78
5504018              2980       0.22
5504018              3002       0.02
7704021              3002       1.25
7704021              2980       0.5
7704021              75C        0.08
*/

[日月路明]

虽然版主给出了答案，但是我觉得还是改数据库结构是上策，版主这样做害人一生

[砸死牛顿的苹果]

好吧,这也算疑难SQL~