CODEWITHSUNDEEP
sqlcase
JohnZ
JohnZ

Reputation: 482

Show additional Column using Case within select

Using SQL 2008, I have this code :

    if (exists (select * from tempdb.INFORMATION_SCHEMA.TABLES where TABLE_NAME = '##tempz'))
begin
drop table ##tempz
end
else
DECLARE @startDate datetime, @enddate datetime, @storename varchar(20), @showstore int, @showcashier int, @showregister int, @showdate int
SET @startDate = '1/1/2011'
SET @enddate = '1/1/2013'
SET @storename = '01'
SET @showstore = 1
SET @showcashier = 1
SET @showregister = 1
SET @showdate = 1

DECLARE @cols AS NVARCHAR(MAX),@colsNull AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.CurrencyDesc)
FROM rpPay p LEFT JOIN RPTrs r ON p.ReceiptNo = r.ReceiptNo LEFT JOIN Currencies c ON c.POSCurrency = LEFT(p.paytype,1)
WHERE r.trsdate >= @startDate AND r.trsdate <= @enddate
FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SELECT @colsNull = STUFF((SELECT DISTINCT ', IsNull(' + QUOTENAME(c.CurrencyDesc) +', 0) as '+ QUOTENAME(c.CurrencyDesc)
FROM rpPay p LEFT JOIN RPTrs r ON p.ReceiptNo = r.ReceiptNo LEFT JOIN Currencies c ON LEFT(p.paytype,1) = c.POSCurrency
WHERE r.trsdate >= @startDate AND r.trsdate <= @enddate
FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @query = 'select date, cashregister, storeid, cashier, '+@colsNull+' into ##tempz
FROM
(SELECT cast(r.trsdate AS DATE) date,c.CurrencyDesc,p.amount,r.cashregister,r.storeid,r.cashier
FROM rpPay p LEFT JOIN RPTrs r ON p.ReceiptNo = r.receiptno LEFT JOIN Currencies c ON LEFT(p.paytype,1) = c.POSCurrency
WHERE r.trsdate >= '''+ convert(varchar(10), @startDate, 101) +''' AND r.trsdate <= '''+ convert(varchar(10), @endDate, 101) +''' 
) p
pivot
(sum(amount) FOR CurrencyDesc in('+@cols+')) piv'
execute(@query)

select * from ##tempz

This returns return

What I would like is that if either one of these :

SET @showstore = 1
SET @showcashier = 1
SET @showregister = 1
SET @showdate = 1

Is 0, then I do not want to display it in the results window. So if @showcashier is 0, then Cashier will not show

Upvotes: 0

Views: 799

Answers (2)

Grzegorz W
Grzegorz W

Reputation: 3517

One way of doing this would be to use stored procedure like this:

CREATE PROCEDURE GetData
    @show tinyint
AS
BEGIN
    IF @show = 1
    BEGIN
        select CurrencyDesc, POSCurrency from Currencies;
    END
    ELSE
        select CurrencyDesc from Currencies;
END

and than execute it like this:

exec GetData 1 -- returns 2 columns

exec GetData 0 -- returns one column

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270341

You can't, using regular SQL. The columns returned by a SQL statement are defined.

You have three alternatives. The first is to select the columns at the application level. The second is to return NULL values (which is what your query would do if the syntax were correct. The third is to use "dynamic" sql. That is, generate a SQL query as a string, and then execute it.

The third option depends on the database you are using.

Upvotes: 2

Related Questions