sql query, each column in result is a different WHERE clause

Question

I'm trying to find the easiest way to return some results based on dates. So for example the table is like this

product     date     qty
corn        4/18/16   5
corn        4/18/16   1
corn        4/15/16   10

I would want the results to be

product    4/15/16       4/18/16
corn        10              6

So I am grouping by the product, sum the qty, but each column in result will be a different WHERE filedate = .

Can someone please point me in the right direction?

This is called *pivoting* - the way to do it will depend on your platform, which you have not stated. — Alex K., Apr 18 '16 at 12:34
What's the expected result if suddenly the row (corn, 4/20/16, 3) is inserted? — jarlh, Apr 18 '16 at 12:36
if a new date is in the source... then when I run my query again it will have a new result column for 4/20/16 — solarissf, Apr 18 '16 at 12:37
PIVOT is one of the most repeated topic here. There are so many post available with PIVOT, PIVOT with dynamic columns, etc. — Abdul Rasheed, Apr 18 '16 at 12:42

Vignesh Kumar A · Accepted Answer · 2016-04-18T12:53:48.150

2

Try like this

SELECT * FROM
(
    SELECT product,date,qty
    FROM Table1
)X 
PIVOT 
(
    SUM(X.qty) 
    FOR date IN([4/18/16],[4/15/16])
)Y

For multiple source, you need to write dynamic query like below

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

;WITH CTE (DATELIST, MAXDATE) AS
(
    SELECT '01/04/2016' DATELIST, '30/04/2016' MAXDATE
    UNION ALL
    SELECT DATEADD(dd, 1, DATELIST), MAXDATE
    FROM CTE
    WHERE DATELIST < MAXDATE
) 
SELECT C.DATELIST
INTO #TempDates
FROM CTE C


SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(convert(CHAR(10), DATELIST, 120)) 
                    FROM #TempDates
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT product,' + @cols + ' FROM 
             (
                SELECT B.product, B.date, B.qty, D.DATELIST, CONVERT(CHAR(10), DATELIST, 120) PivotDate
                FROM #TempDates D
                LEFT JOIN YourTable B ON D.DATELIST BETWEEN ''01/04/2016'' AND ''30/04/2016''
            ) X
            PIVOT 
            (
                SUM(qty)
                FOR PivotDate IN (' + @cols + ')
            ) P '

EXECUTE(@query)

edited Apr 18 '16 at 12:53

answered Apr 18 '16 at 12:37

Vignesh Kumar A

27,863
13
63
115

1

thanks Vignesh... is there a way I can say for EVERY DAY IN APRIL... rather than listing each data specifically? – solarissf Apr 18 '16 at 12:38
Two seconds later OP wrote the comment: "if a new date is in the source... then when I run my query again it will have a new result column". – jarlh Apr 18 '16 at 12:38
@solarissf Yes it's possible. you have to write dynamic sql – Vignesh Kumar A Apr 18 '16 at 12:40
I'm trying to dissect your sample... which section do I replace with my tables? – solarissf Apr 18 '16 at 13:11
I don't understand cols... I see that STUFF creates a string, QUOTENAME adds delimiters. What is the purpose of this section? Is cols just a list of the dates I want as columns? – solarissf Apr 18 '16 at 13:15
@solarissf yes. cols will have the list of dates as you said – Vignesh Kumar A Apr 19 '16 at 03:41
1

thank you... this seems to be what I was looking for. I haven't got the dynamic sql working yet but I'll keep researching... either way, this looks like the solution... thanks! – solarissf Apr 20 '16 at 12:34

sql query, each column in result is a different WHERE clause

1 Answers1