SQL /动态SQL-如果不存在则添加列 [英] SQL/Dynamic SQL - Add column if it does not exist

问题描述

我在下面使用SQL查询：

I have the SQL query below:

create table #temp
(
Account varchar(5),
category varchar(6),
amount money
)

insert into #temp values ('A001', 'ABC-CO', 1000.00)
insert into #temp values ('A002', 'DEF-CR', 500.00)
insert into #temp values ('A002', 'GHI-PR', 800.00)
insert into #temp values ('A003', 'DEF', 700.00)
insert into #temp values ('A004', 'ABC-PR', 1100.00)


DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(REPLACE(REPLACE(REPLACE(c.category, '-PR',''),'- 
CO',''),'-CR',''))
    FROM #temp c
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)') 
,1,1,'')

set @query = 'SELECT Account, ' + @cols + ', (case when DEF is not NULL and GHI is not NULL then 1 
                                        when ABC is not NULL then 1
                                        else 0 end) as Outcome from 
    (
        select Account
            , amount
            , REPLACE(REPLACE(REPLACE(category, ''-PR'',''''),''-CO'',''''),''-CR'','''') as 
category
        from #temp 
   ) x
    pivot 
    (
         max(amount)
        for category in (' + @cols + ')
    ) p '


execute(@query)

drop table #temp

哪个生成下表：

 Account    ABC      DEF     GHI        Outcome
 A001       1000.00  NULL    NULL       1
 A002       NULL     500.00  800.00     1
 A003       NULL     700.00  NULL       0
 A004       1100.00  NULL    NULL       1

我的问题是case语句是基于已知关系建立的，其中某些列未出现在查询中。

My issue is that the case statement is built off a known relationship where some of the columns do not appear in the query.

例如，如果我添加到case语句中：

For example if I added to the case statement:

case when DEF is not NULL and JKL is not NULL then 1

所需的输出是因为JKL不t存在，则为0。

The desired output is that since JKL doesn't exist, then 0.

由于JKL不存在，因此查询错误。如何编辑上面的代码以查看JKL列的表，如果不存在，则添加具有NULL值的JKL列，然后引用case语句？

Since JKL doesn't exist though, the query errors. How can I edit the code above to review table for column JKL, if it does not exist then add column JKL with NULL values, and then reference in case statement?

推荐答案

列从透视的类别值中得出。只需为金额为NULL的帐户插入虚拟DEF，GHI，ABC类别即可。这些空值不会影响结果，并且如果没有一个帐户具有这些类别，则将生成DEF，GHI，ABC列。

the "columns" derive from the pivoted category values. Just insert dummy DEF, G ABC categories for an account with NULL amount. Those null values will not affect the result and will generate the DEF, G ABC columns if none of the accounts has those categories.

..........................
insert into #temp values ('A004', 'ABC-PR', 1100.00)


insert into #temp(Account, category, amount)
select a.Account, c.category, null
from
(
select top (1) Account from #temp
) as a 
cross join (values('DEF'), ('GHI'), ('ABC'), ('JKL')) AS c(category);

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);
.........................

这篇关于SQL /动态SQL-如果不存在则添加列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！