Clause CASE in PIVOT? SQL Server 2008 r2

Navigation
0

Good people,

I have this query with a pivot that gives me this data: 

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @date1 AS NVARCHAR(MAX),
    @date2 AS NVARCHAR(MAX),
    @space NVARCHAR (10),
    @x NVARCHAR (10) 

    set @date1 = '''01/06/2013'''
    set @date2 = '''30/06/2013'''
    set @space = ''''''
    set @x = '''X'''

select @cols = STUFF((SELECT ',' + QUOTENAME(DAY(a.FECHA))
                    from dbo.CALENDARIO AS a
                    WHERE a.FECHA between @date1 AND @date2 
                    group by a.FECHA
                    order by a.FECHA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

SET @query = 'SELECT pvt.*
FROM (
    SELECT 
       left(datename(day,DATEFIELD),3)as [day],
        FIELD1 AS FIELD1
    FROM dbo.TABLEFROMDDBB
    WHERE FIELD2 = 0 
    AND FIELD1 = 0
    AND DATEFIELD between ' + @date1 + ' AND ' + @date2 + ' 
    AND FIELD3 = 'xxx'
    AND FIELD4 = 123456789
    AND FIELD5 = 'xxxx'
) as s
PIVOT
(
    COUNT(FIELD1)
    FOR [day] IN (' + @cols + ')
)AS pvt' 

execute(@query);

What the client needs is that instead of 1 and 0 you see an X when it is a 1 and null when it is 0, I know that this must be applied with a case like this: 

CASE WHEN FIELD1 IS not null THEN 'X'  ELSE ''  END AS FIELD1

I have tried several formulas but they all return the error that the FIELD1 field is not recognized

I have introduced it in this part of the query: 

SET @query = 'SELECT ' + @cols + ' AS [day], CASE WHEN FIELD1 IS NOT null THEN 'X'  ELSE ''  END AS FIELD1

but, as I say, I get the error that you do not know the fields.

All kinds of solutions are welcome.

Forearm Thanks:)

    
asked by Tony Waters 16.11.2017 в 15:00
source

1 answer

1

You could try to create that expression CASE dynamically as well. Something similar to: 

DECLARE @cols AS NVARCHAR(MAX),
    @cols2 AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @date1 AS NVARCHAR(MAX),
    @date2 AS NVARCHAR(MAX),
    @space NVARCHAR (10),
    @x NVARCHAR (10) 

    set @date1 = '''01/06/2013'''
    set @date2 = '''30/06/2013'''
    set @space = ''''''
    set @x = '''X'''

select @cols = STUFF((SELECT ',' + QUOTENAME(DAY(a.FECHA))
                    from dbo.CALENDARIO AS a
                    WHERE a.FECHA between @date1 AND @date2 
                    group by a.FECHA
                    order by a.FECHA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')


select @cols2 = STUFF((SELECT ',CASE WHEN ' + QUOTENAME(DAY(a.FECHA)) + ' IS NOT NULL THEN ''X'' ELSE '' END AS' + QUOTENAME(DAY(a.FECHA)) 
                    from dbo.CALENDARIO AS a
                    WHERE a.FECHA between @date1 AND @date2 
                    group by a.FECHA
                    order by a.FECHA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

SET @query = 'SELECT ' + @cols2 + '
FROM (
    SELECT 
       left(datename(day,DATEFIELD),3)as [day],
        FIELD1 AS FIELD1
    FROM dbo.TABLEFROMDDBB
    WHERE FIELD2 = 0 
    AND FIELD1 = 0
    AND DATEFIELD between ' + @date1 + ' AND ' + @date2 + ' 
    AND FIELD3 = 'xxx'
    AND FIELD4 = 123456789
    AND FIELD5 = 'xxxx'
) as s
PIVOT
(
    COUNT(FIELD1)
    FOR [day] IN (' + @cols + ')
)AS pvt' 

execute(@query);
    
answered by 16.11.2017 в 15:19
Assign value to hidden div How to start from the second property of an object in javascript?