SQL将数据合并到单行

最后发布: 2014-06-17 18:55:57


问题

我建立了一个表,该表跟踪对用户帐户的更改。

它具有IDUserAccountNoOldValNewValChangeColumnName列。

我有一个与此类似的查询设置:

    Select case 
    when ChangeColumnName = 'Address1' then NewVal else '' end as Address1,
    when ChangeColumnName = 'Address2' then NewVal else '' end as Address2,
    when ChangeColumnName = 'City' then NewVal else '' end as City,
    when ChangeColumnName = 'State' then NewVal else '' end as State,
    when ChangeColumnName = 'Zip' then NewVal else '' end as Zip,
    when ChangeColumnName = 'Phone' then NewVal else '' end as Phone
    from table
    Where (Conditions)

如果有人更改了城市,州和邮政编码,则表中有3个条目。 当我运行此查询时,我得到3行返回。 我想将它们全部排成一排,但还没弄清楚怎么做。

当我尝试按照其他帖子中的建议将groupby与max(colname)一起使用时,它提供了最大NewVal值,因此我最终在Phone列中使用了电子邮件地址。

是否可以在SQL 2008中执行而无需重新格式化整个表?

sql sql-server sql-server-2008
回答

我建议您使用数据透视命令,使用此脚本并让我知道:

IF OBJECT_ID('_temp') IS NOT NULL DROP TABLE _temp

SELECT *
    INTO _temp
FROM (
        Select 'PostalCode' AS ChangeColumnName, '95100' AS NewValue UNION ALL
        Select 'City' AS ChangeColumnName, 'Argenteuil' AS NewValue UNION ALL
        Select 'LastName' AS ChangeColumnName, 'DAOUI' AS NewValue UNION ALL
        Select 'FirstName' AS ChangeColumnName, 'Youssef' AS NewValue UNION ALL
        Select 'Phone Number' AS ChangeColumnName, '00212 6 60 93 36 12' AS NewValue 
 ) AS Temp

    DECLARE  @v_ListeColonnes           VARCHAR(MAX) = ''
            ,@v_sql                     VARCHAR(MAX) = ''           

    SELECT @v_ListeColonnes         = @v_ListeColonnes + ',' + QUOTENAME(ChangeColumnName)
    FROM _temp

    IF LEN(@v_ListeColonnes) > 1
        BEGIN
            SELECT @v_ListeColonnes = RIGHT(@v_ListeColonnes, LEN(@v_ListeColonnes)-1)
            SET @v_sql  =    'SELECT '+CHAR(13)
                            +'      ' + @v_ListeColonnes + ' '+CHAR(13)
                            +'FROM _temp '+CHAR(13)
                            +'PIVOT (MAX(NewValue) '+CHAR(13)
                            +'  FOR ChangeColumnName in(' + @v_ListeColonnes + ')) as pvt        '+CHAR(13)
            EXEC(@v_sql)
        END

IF OBJECT_ID('_temp') IS NOT NULL DROP TABLE _temp

我希望这能帮到您。


回答

尝试这个

create table #t
(
id int,
userAccountNo int,
oldVal varchar(255),
newVal varchar(255),
changeColName varchar(255)
);

insert #t values (1, 1, '123 main st', '123 s. main st.', 'Address1'),
  (2, 1, 'Springville', 'Springfield', 'City'),
  (3, 1, 'Springfield', 'N. Springfield', 'City'),
  (4, 2, '12345', '12346', 'Zip');

with U as (select distinct userAccountNo from #t),
    Address1 as (select userAccountNo, newVal from #t as T1 where changeColName = 'Address1' and id >=ALL
    (select id from #t as T2 where T1.userAccountNo = T2.userAccountNo and T1.changeColName = T2.changeColName)),
    City as (select userAccountNo, newVal from #t as T1 where changeColName = 'City' and id >=ALL
    (select id from #t as T2 where T1.userAccountNo = T2.userAccountNo and T1.changeColName = T2.changeColName)),
    Zip as  (select userAccountNo, newVal from #t as T1 where changeColName = 'Zip' and id >=ALL
    (select id from #t as T2 where T1.userAccountNo = T2.userAccountNo and T1.changeColName = T2.changeColName))
select
U.userAccountNo,
A1.newVal as [Address1],
C.newVal as [City],
Z.newVal as [Zip]
from
U
full outer join Address1 as A1 on U.userAccountNo = A1.userAccountNo
full outer join City as C on U.userAccountNo = C.userAccountNo
full outer join Zip as Z on U.userAccountNo = Z.userAccountNo;

而且,如果看起来可行,则可以扩展为涵盖您的所有列。


回答

我假设您需要一行和一列进行所有更改,它适用于任何数量的更改列。

SQL现场测试

declare @changes as varchar(max)
declare @UserAccountNo int

set @UserAccountNo=1
set @changes=''

select @changes=@changes + ColumnChanged +'-'    
from changes where UserAccountNo=@UserAccountNo

select @UserAccountNo 'UserAccountNo', @changes 'Changes'