34,587
社区成员
想请教一下SQLserver如何在行转列之后保留重复值,希望有大佬可以解惑一下。
问题描述:
TABLE_A是一个列转行之后的表(如下),在这个表中数据可能会有重复的code、字段名,但是字段值是不同的。执行到这一步结果是准确的。
执行行转列之后得到了TABLE_B表,查询这个表中的数据发现重复值被去除了,name1和name2显示的是321和4321,感觉是覆盖了上一条code相同数据的值(123和1234)。我想知道如何保留重复值,实现TABLE_C的效果呢?
一、表数据:
TABLE_A
code | 字段名 | 字段值 |
11 | name1 | 123 |
11 | name2 | 1234 |
11 | name1 | 321 |
11 | name2 | 4321 |
TABLE_B
code | name1 | name2 |
11 | 321 | 4321 |
想实现的效果:TABLE_C
code | name1 | name2 |
11 | 123 | 1234 |
11 | 321 | 4321 |
二、代码:
DECLARE @SQL_STR VARCHAR (MAX);-- 要执行的SQL
DECLARE @SQL_COL VARCHAR (MAX);
--获取temp2表的所有列名(除code)
select @SQL_COL= ISNULL( @SQL_COL + ',', '' ) + QUOTENAME(name ) from tempdb.dbo.syscolumns where id=object_id('tempdb..#temp2')AND Name NOT IN('code')
ORDER BY Colid
select @SQL_COL;
--20220905将行转列后的数据从TABLE_A表保存到新表TABLE_B(暂存表)
select @SQL_STR = '
SELECT * into TABLE_BFROM [TABLE_A] A
PIVOT (max([字段值]) FOR [字段名] IN ( '+ @SQL_COL + ')) AS PVT
'
PRINT ( @SQL_STR );
EXEC ( @SQL_STR );-- 执行查询
谢谢!求大佬救命!QAQ...
主要的原因是:根据常规写法,group by code 。但是它都是11,所以只能处理一行。
解决办法:加序号,区分这两行。
;with src as
(
select code = '11',字段名 = 'name1',字段值 = 123 union all
select code = '11',字段名 = 'name2',字段值 = 1234 union all
select code = '11',字段名 = 'name1',字段值 = 321 union all
select code = '11',字段名 = 'name2',字段值 = 4321
)
,t1 as
(
select*,row_number()over(partition by code,字段名 order by code)as rn
from src
)
select t1.code
,max(case when 字段名 = 'name1' then 字段值 else 0 end) as name1
,max(case when 字段名 = 'name2' then 字段值 else 0 end) as name2
from t1
group by t1.code,rn
有个笨办法可以实现。但不得不说你花样好多,冥冥之中有一种感觉,整个需求似乎走弯路了。
--创建示例表
create table table_a (code int,字段名 nvarchar(50),字段值 int)
--写入示例数据
insert into table_a values(11,'name1',123),(11,'name2',1234),(11,'name1',321),(11,'name2',4321)
--查询
select
code
,name1
,name2
from (
select
code,字段名
,字段值
,row = ROW_NUMBER()over(partition by code,字段名 order by row)
from (
select
*
,row = ROW_NUMBER()over(order by (select 1))
from table_a
)T
)T pivot(sum(字段值) for 字段名 in([name1],[name2]))t
code | name1 | name2 |
---|---|---|
11 | 123 | 1234 |
11 | 321 | 4321 |