CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2group-bypivot
Lynn
Lynn

Reputation: 3660

Getting Data In to a pivot table

Well I really tried to use the stuff that I was taught in yesterday’s question and answers from @AaronBertrand to a similar type issue. I am using in Microsoft SQL Server 2008 R2

I have data that looks like this:

SalesPersonID TransDate   Order     DateTotal
1108          8/2/2013    231.95    7713.8
1108          8/2/2013    5805.15   7713.8
1108          8/2/2013    1676.70   7713.8
1108          8/3/2013    159.95    3635.35
1108          8/3/2013    468.90    3635.35
1108          8/3/2013    1160.85   3635.35
1108          8/3/2013    209.95    3635.35
1108          8/3/2013    1161.85   3635.35
1108          8/3/2013    473.85    3635.35
1108          8/4/2013    149.98    3151.68
1108          8/4/2013    793.95    3151.68
1108          8/4/2013    55.00     3151.68
1108          8/4/2013    198.95    3151.68
1108          8/4/2013    398.00    3151.68
1108          8/4/2013    1255.85   3151.68
1108          8/4/2013    299.95    3151.68
1108          8/9/2013    223.95    1413.8
1108          8/9/2013    59.95     1413.8
1108          8/9/2013    1129.90   1413.8
1108          8/30/2013   1396.43   1396.43
1108          8/31/2013   89.95     1735.65
1108          8/31/2013   495.95    1735.65
1108          8/31/2013   495.95    1735.65
1108          8/31/2013   633.85    1735.65
1108          8/31/2013   19.95     1735.65
1205          8/3/2013    2389.09   2389.09

And using the answer that I got from an earlier question, [Here], I guess I don’t completely understand the SQL....

SO .....

I am needing the data to come out something like this:

SalesPersonID   1       2       3       4       5     6     7     8     9      ….  29    30      31
1108            0.00    7713.80 3635.35 3151.68 0.00  0.00  0.00  0.00  1413.80    0.00  1396.43 1735.65
1205            0.00    0.00    2389.09 0.00    0.00  0.00  0.00  0.00  0.00       0.00  0.00    0.00

The numbers across the top are the days of the selected month, I am using August, If I selected June, it would only have 30.

I use this code to get my first and last days and dates.

DECLARE @BeginDate AS VARCHAR(10), @EndDate AS VARCHAR(10), @SelectedMonthDays AS int
SET @BeginDate='08/15/13'

-- Last Day of Current Month
Set @SelectedMonthDays = DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@BeginDate)+1,0)))

-- Last day Date of Current Month
SET @EndDate=cast(convert(date,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@BeginDate)+1,0))) as varchar(10))

-- Convert input date to the first day Date of current month
Set @BeginDate = cast(convert(date,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@BeginDate),0))) as varchar(10))

To get days of the currently selected month, I have this code:

SELECT xhours = number FROM Master..spt_values WHERE type = N'P' and number between 1 and @SelectedMonthDays ORDER BY number

Thanks for any help that you all can give.

Upvotes: 0

Views: 529

Answers (1)

Taryn
Taryn

Reputation: 247720

This type of data transformation is called a PIVOT.

If you knew the values that you wanted ahead of time, then you could hard-code the query and the basic syntax would be:

SELECT salespersonid, 
       COALESCE([1], 0.00)  AS [1], 
       COALESCE([2], 0.00)  AS [2], 
       COALESCE([3], 0.00)  AS [3], 
       COALESCE([4], 0.00)  AS [4]
FROM   
(
    SELECT salespersonid, 
        [order], 
        Datepart(day, transdate) day 
    FROM   yourtable 
    WHERE  transdate >= '2013-08-01' 
        AND transdate <= '2013-08-31'
) x 
PIVOT 
(
    Sum([order]) 
    FOR day IN ([1], [2], [3], [4]) 
) p

But your situation where you want to adjust the result based on the number of days in each month you will want to look at using dynamic SQL. You can use your existing query using the Master..spt_values to get the list of dates you will just have to place that list in a string. The dynamic SQL code will be similar to:

DECLARE @BeginDate AS VARCHAR(10), @EndDate AS VARCHAR(10), @SelectedMonthDays AS int
SET @BeginDate='08/15/13'

-- Last Day of Current Month
Set @SelectedMonthDays = DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@BeginDate)+1,0)))

-- Last day Date of Current Month
SET @EndDate=cast(convert(date,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@BeginDate)+1,0))) as varchar(10))

-- Convert input date to the first day Date of current month
Set @BeginDate = cast(convert(date,DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@BeginDate),0))) as varchar(10))


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

select @cols = STUFF((SELECT ',' + QUOTENAME(number) 
                    from Master..spt_values 
                    WHERE type = N'P' 
                      and number between 1 and @SelectedMonthDays 
                    group by number
                    ORDER BY number
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsNull = STUFF((SELECT ', coalesce(' + QUOTENAME(number)+', 0.00) as '+QUOTENAME(number)
                    from Master..spt_values 
                    WHERE type = N'P' 
                      and number between 1 and @SelectedMonthDays 
                    group by number
                    ORDER BY number
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT SalesPersonID, ' + @colsNull + ' 
            from 
            (
                select SalesPersonID, [Order], 
                  datepart(day, TransDate) day
                from yourtable
                where TransDate>= '''+convert(varchar(10), @BeginDate, 120)+'''
                  and TransDate<= '''+convert(varchar(10), @EndDate, 120)+'''
            ) x
            pivot 
            (
                sum([Order])
                for day in (' + @cols + ')
            ) p '


execute sp_executesql @query;

See SQL Fiddle with Demo. This gives a result:

| SALESPERSONID | 1 |      2 |       3 |       4 | 5 | 6 | 7 | 8 |      9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 |      30 |      31 |
|          1108 | 0 | 7713.8 | 3635.35 | 3151.68 | 0 | 0 | 0 | 0 | 1413.8 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 | 1396.43 | 1735.65 |
|          1205 | 0 |      0 | 2389.09 |       0 | 0 | 0 | 0 | 0 |      0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |       0 |       0 |

Upvotes: 3

Related Questions