TSQL查询-列到行

Question

我有后遗症。

USE [tempdb]
GO

CREATE TABLE #Table (id int, row# int, Info varchar(10),
                        clm11 varchar(10), 
                        clm21 varchar(10), 
                        clm31 varchar(10),
                        clm41 varchar(10), 
                        clm51 varchar(10), 
                            clm12 varchar(10), 
                            clm22 varchar(10), 
                            clm32 varchar(10),
                            clm42 varchar(10), 
                            clm52 varchar(10), 
                                clm13 varchar(10), 
                                clm23 varchar(10), 
                                clm33 varchar(10),
                                clm43 varchar(10), 
                                clm53 varchar(10))
INSERT INTO #Table
SELECT 1, 100, 'Text', 'Col1', 'Col2', 'Col3', 'Col4', 'Col5','Col11', 'Col12', 'Col13', 'Col14', 'Col15','Col21', 'Col22', 'Col23', 'Col24', 'Col25'
UNION ALL
SELECT 2, 100, 'Text', 'Col1', 'Col2', 'Col3', 'Col4', 'Col5', NULL, NULL, NULL, NULL, NULL,'Col21', 'Col22', 'Col23', 'Col24', 'Col25'
UNION ALL
SELECT 3, 100, 'Text',  'Col1', 'Col2', 'Col3', 'Col4', 'Col5','Col11', 'Col12', 'Col13', 'Col14', 'Col15', NULL, NULL, NULL, NULL, NULL
UNION ALL
SELECT 4, 100, 'Text',  'Col1', 'Col2', 'Col3', 'Col4', 'Col5', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
UNION ALL
SELECT 5, 100, 'Text',  NULL, NULL, NULL, NULL, NULL, 'Col11', 'Col12', 'Col13', 'Col14', 'Col15', 'Col21', 'Col22', 'Col23', 'Col24', 'Col25'
UNION ALL
SELECT 6, 100, 'Text',  NULL, NULL, NULL, NULL, NULL, 'Col11', 'Col12', 'Col13', 'Col14', 'Col15', NULL, NULL, NULL, NULL, NULL
UNION ALL
SELECT 7, 100, 'Text',  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Col21', 'Col22', 'Col23', 'Col24', 'Col25'

SELECT * FROM #Table


/* Desired output

ID  Clm1    Clm2    Clm3    Clm4    Clm5
1   Col1    Col2    Col3    Col4    Col5
1   Col11   Col12   Col12   Col14   Col15
1   Col21   Col22   Col23   Col24   Col25
2   Col1    Col2    Col3    Col4    Col5
2   Col21   Col22   Col23   Col24   Col25
3   Col1    Col2    Col3    Col4    Col5
3   Col11   Col12   Col13   Col14   Col15
4   Col1    Col2    Col3    Col4    Col5
5   Col11   Col12   Col13   Col14   Col15
5   Col21   Col22   Col23   Col24   Col25
6   Col11   Col12   Col13   Col14   Col15
7   Col21   Col22   Col23   Col24   Col25

*/


--- My Try
SELECT Id, FieldCode, FieldValue 

FROM ( SELECT id, clm11, clm21, clm31, clm41, clm51, clm12, clm22, clm32, clm42, clm52, clm13, clm23, clm33, clm43, clm53 FROM #Table ) MyTable

UNPIVOT
(FieldCode FOR FieldCodes IN ( clm11, clm21, clm31, clm41, clm12, clm22, clm32, clm42, clm13, clm23, clm33, clm43))AS CODE

UNPIVOT
(FieldValue FOR FieldValues IN (clm51, clm52, clm53))AS FieldValues
WHERE RIGHT(FieldCodes,1) = RIGHT(FieldValues,1)


DROP TABLE #Table

我试图将ID和Clm11取为Clm53 (5列集)，如果集合(5 Col's)有值(非空集)，则将它们与ID放在一个单独的记录中，如“期望输出”部分所示。我刚刚显示了15列(Clm11-Clm51、Clm12-Clm52和Clm13-Clm53)，但是可以设置更多的列。

请帮帮忙。

临时更新：

select *
from (select id,
             (case when n = 1 then clm11
                   when n = 2 then clm21
                   when n = 3 then clm31
                   when n = 4 then clm41
                   when n = 5 then clm51
              end) as col1,
              (case when n = 1 then clm12
                   when n = 2 then clm22
                   when n = 3 then clm32
                   when n = 4 then clm42
                   when n = 5 then clm52
              end) as col2,
              (case when n = 1 then clm13
                   when n = 2 then clm23
                   when n = 3 then clm33
                   when n = 4 then clm43
                   when n = 5 then clm53
              end) as col3
      from #table t cross join
           (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5) n
     ) t
where coalesce(col1, col2, col3) is not null;

Answer 1

我想你只要用一系列的union all就可以做到这一点

select id, Col11, Col12, Col13, Col14, Col15
from #table
where col11 is not null or col12 is not null or col13 is not null or
      col14 is not null or col15 is not null
union all
select id, Col21, Col22, Col23, Col24, Col25
from #table
where col21 is not null or col22 is not null or col23 is not null or
      col24 is not null or col25 is not null
union all
select id, Col31, Col32, Col33, Col34, Col35
from #table
where col31 is not null or col32 is not null or col33 is not null or
      col34 is not null or col35 is not null;

编辑：

是的，有一种方法可以通过单表扫描来处理这个问题。其想法是使用一个单独的计数器表和大量的案例陈述。下面是代码结构：

select *
from (select id,
             (case when n = 1 then col11
                   when n = 2 then col21
                   when n = 3 then col31
              end) as col1,
             . . .
      from #table t cross join
           (select 1 as n union all select 2 union all select 3) n
     ) t
where coalesce(col1, col2, col3, col4, col5) is not null;

这使用子查询来定义列，因此where子句不必重复丑陋的case逻辑。

编辑2：

完整的查询看起来像5个case语句，每个语句包含三个子句，而不是三个case语句：

select *
from (select id,
             (case when n = 1 then clm11
                   when n = 2 then clm12
                   when n = 3 then clm13
              end) as col1,
              (case when n = 1 then clm21
                   when n = 2 then clm23
                   when n = 3 then clm23
              end) as col2,
              (case when n = 1 then clm31
                   when n = 2 then clm32
                   when n = 3 then clm33
              end) as col3,
              (case when n = 1 then clm41
                   when n = 2 then clm42
                   when n = 3 then clm43
              end) as col4,
              (case when n = 1 then clm51
                   when n = 2 then clm52
                   when n = 3 then clm53
              end) as col5
      from t cross join
           (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5) n
     ) t
where coalesce(col1, col2, col3, col4, col5) is not null;

SQL是这里。