Sql-server – Select with dynamic columns

dynamic-sqlpivotsql serversql-server-2008-r2

I have an InvoiceHeader, InvoiceItem tables

InvoiceHeader content:

InvoiceId   InvoiceNumber   BusinessId  BusinessName    BranchId
        1               1           10       BSN INC    1
        2               2           10       BSN INC    2
        3               3           23    SAMPLE INC    1
        4               4           45   ANOTHER INC    1
        5               5           45   ANOTHER INC    3

InvoiceItem content:

InvoiceId   ItemId  ItemCode    Name            Quantity
        1        1    SNACK1    GOOD SNACKS     150
        1        2    SNACK2    SNACKERS        120
        2        1    SNACK1    GOOD SNACKS     100
        2        2    SNACK2    SNACKERS        150
        3        1    SNACK1    GOOD SNACKS     150
        3        2    SNACK2    SNACKERS        150
        3        3    SNACK3    BIG SNACKS      110
        4        1    SNACK1    GOOD SNACKS     90
        4        2    SNACK2    SNACKERS        80
        4        3    SNACK3    BIG SNACKS      120
        5        1    SNACK1    GOOD SNACKS     50
        5        2    SNACK2    SNACKERS        70
        5        3    SNACK3    BIG SNACKS      90
        5        4    SNACK4    TASTY SNACKS    70

Here is the sqlfiddle: http://sqlfiddle.com/#!18/aa5fa/6

Now the result that I want is like this:

InvoiceNumber   BusinessName    BusinnessBranch SNACK1  SNACK2  SNACK3  SNACK4  TotalItems
    1           BSN INC         Branch1         150     120     0       0       270
    2           BSN INC         Branch2         100     150     0       0       250
    3           SAMPLE INC      Branch1         150     150     110     0       410
    4           ANOTHER INC     Branch1         90      80      120     0       290
    5           ANOTHER INC     Branch3         50      70      90      70      280
                                                                                1500

As you can see the items code from the invoices are added as columns to count the items, in the last column the items total from that invoice, and the last row it shows the total items from all selected invoices.

The number of transposed columns can be more than that, can reach even more than 10.

Best Answer

This query will give you what you need, or at least get you close.

;WITH CTE_Items AS
  (
    SELECT InvoiceId
        , COALESCE(SNACK1, 0) AS SNACK1
        , COALESCE(SNACK2, 0) AS SNACK2
        , COALESCE(SNACK3, 0) AS SNACK3
        , COALESCE(SNACK4, 0) AS SNACK4
    FROM (SELECT InvoiceId, ItemCode, Quantity FROM dbo.InvoiceItem AS II) AS I
      PIVOT (SUM(Quantity) FOR ItemCode IN ([SNACK1], [SNACK2], [SNACK3], [SNACK4])) AS P
    )
SELECT IH.InvoiceId
    , IH.BusinessName
    , 'Branch' + CONVERT(VARCHAR(10), IH.BranchID) AS BusinessBranch
    , I.SNACK1
    , I.SNACK2
    , I.SNACK3
    , I.SNACK4
    , TotalSnacks = I.SNACK1 + I.SNACK2 + I.SNACK3 + I.SNACK4
FROM dbo.InvoiceHeader AS IH
    LEFT OUTER JOIN CTE_Items AS I ON I.InvoiceID = IH.InvoiceID

Related Solutions

Sql-server – Dynamic Number of Columns in Pivot Table

Working from Aaron Bertrand's Script to create dynamic PIVOT queries in SQL Server and with a sample data set defined thus:

if object_id('#ObtainedMarkEntry') is not null drop table #ObtainedMarkEntry;
create table #ObtainedMarkEntry(
    StudentID       int not null, 
    SubjectID       int not null, 
    ObtainedMarks       int not null
);
insert #ObtainedMarkEntry(StudentID, SubjectID, ObtainedMarks)
values
        (1,100,90) ,(1,101,70)
       ,(2,100,65) ,(2,300,55)
;

then this SQL:

declare @columnsSrc nvarchar(max) = N'' 
       ,@columnsDst nvarchar(max) = N'' 
       ,@sql        nvarchar(max)
       ,@KeyColumns nvarchar(max) = N'StudentID'
       ,@compatibility int = (
            select top 1 compatibility_level from sys.databases
            where name = db_name()
            order by Name
       );
declare @GroupBy nvarchar(max) = 
--case when @compatibility <= 90 
--   then case when len(@KeyColumns)=0 then '' else 'group by ' + @KeyColumns + ' with rollup' end
--   else case when len(@KeyColumns)=0 then '' else 'group by rollup (' + @KeyColumns + ')'    end
--end
case when len(@KeyColumns)=0 then '' else 'group by ' + @KeyColumns end

;

select  
    @columnsSrc += nchar(10) + N',' + quotename(SubjectID)
   ,@columnsDst += nchar(10) + N',sum(isnull(' + quotename(SubjectID) + N',0)) as ' 
                                               + quotename(SubjectID)
  from (
      select SubjectID
      from #ObtainedMarkEntry
      group by SubjectID
  ) as x
  order by x.SubjectID
;

set @sql = N'
select ' + 
  case when len(@KeyColumns)=0 then '' else @KeyColumns + ',' end +
  STUFF(@columnsDst, 1, 2, '') + '
from (
  select' + nchar(10) + 
    case when len(@KeyColumns)=0 then '' else @KeyColumns + ',' end +
'      SubjectID, ObtainedMarks
   from #ObtainedMarkEntry
) as j
pivot (
  sum(ObtainedMarks) for SubjectID in ('
  + stuff(replace(@columnsSrc, ',p.[', ',['), 2, 1, '')
  + ')
) as p' + nchar(10) +
@GroupBy +
';'
;

--print @sql;
exec sp_executesql @sql;
go

yields as desired:

StudentID   100         101         300
----------- ----------- ----------- -----------
1           90          70          0
2           65          0           55

Sql-server – Dynamic SELECT and place result in variable columns

Adding an example to Michael's answer. The problem with the PIVOT is two fold. 1st, it wants to aggregate. You can get around this by defining your dataset to be distinct and using MAX or MIN functions. But your example above makes that impossible, due to a user being able to have multiple answer sets for a given question. You would end up with only one row with the two datasets aggregated for the dupe user. To get around this I added a "question_set" field, adding a date (distinct enough for this example) to each question/answer set.

2nd, you still have to define the pivoted fields. If you have 50 questions, that's 50 definitions. And if you add a question, you will have to add the definition to the query. Using a loop, I created a question list dynamically and inserted it into the pivot query. Hope this helps.

--> Create the test data
if OBJECT_ID('QuestionAnswers','u') is not null
    drop table QuestionAnswers

create Table  QuestionAnswers
(
id int identity,
question_set date, --> need something to delineate repeats
user_id varchar(255),
question_id int,
answer varchar(255)
)

if OBJECT_ID('Questions','u') is not null
    drop table Questions

create Table  Questions
(
id int identity,
question_id int,
question_name varchar(255)
)
go

with CTEquestion
as
(
    select 1 QID
    union all
    Select QID+1
    from CTEquestion
    where QID < 11
)
insert questions
select QID, 'Question'+cast(QID as varchar(50))
from CTEquestion


insert QuestionAnswers
values ('2015-04-23', 'a1', 1, 'Canswer1')
    ,  ('2015-04-23', 'a1', 2, 'Ianswer2')
    ,  ('2015-04-23', 'a1', 3, 'Canswer3')
    ,  ('2015-04-23', 'a1', 4, 'Canswer4')
    ,  ('2015-04-23', 'a1', 5, 'Ianswer5')
    ,  ('2015-04-23', 'a1', 6, 'Ianswer6')
    ,  ('2015-04-23', 'a1', 7, 'Canswer7')
    ,  ('2015-04-23', 'a1', 8, 'Canswer8')
    ,  ('2015-04-23', 'a1', 9, 'Canswer9')
    ,  ('2015-04-23', 'a1', 10,'Canswer10')

insert QuestionAnswers
values (CONVERT(DATE, GETDATE()), 'b2', 1, 'Canswer1')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 2, 'Canswer2')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 3, 'Canswer3')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 4, 'Canswer4')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 5, 'Canswer5')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 6, 'Ianswer6')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 7, 'Canswer7')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 8, 'Canswer8')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 9, 'Canswer9')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 10, 'Ianswer10')

insert QuestionAnswers
values (CONVERT(DATE, GETDATE()), 'c3', 1, 'Ianswer1')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 2, 'Ianswer2')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 3, 'Canswer3')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 4, 'Ianswer4')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 5, 'Canswer5')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 6, 'Ianswer6')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 7, 'Canswer7')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 8, 'Canswer8')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 9, 'Canswer9')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 10, 'Ianswer10')

insert QuestionAnswers
values (CONVERT(DATE, GETDATE()), 'a1', 1, 'Canswer1')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 2, 'Ianswer2')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 3, 'Canswer3')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 4, 'Canswer4')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 5, 'Canswer5')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 6, 'Canswer6')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 7, 'Canswer7')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 8, 'Canswer8')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 9, 'Canswer9')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 10, 'Ianswer10')
-->End test data creation

--straight join
select qa.user_id, qa.question_set, q.question_id, qa.answer
from Questions q
    join QuestionAnswers qa on qa.question_id=q.question_id
order by qa.user_id


--dynamic pivot 
DECLARE 
    @questionList varchar(max)
, @maxQID int
, @qid int

select @questionList='',@maxQID = MAX(question_id), @qid= MIN(question_id)
FROM Questions

while @qid <= @maxQID
begin
    set @questionList=@questionList+'['+cast(@qid as varchar(10))+']'

    select @qid=min(question_id)
    from Questions
    where question_id > @qid

    if @qid<=@maxQID
        set @questionList=@questionList+', '
end

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = N'
select user_id, '+@questionList+'
from
(select q.question_id, qa.question_set, qa.user_id, qa.answer
from Questions q
    join QuestionAnswers qa on qa.question_id=q.question_id) x
PIVOT
(
max(answer)
FOR question_id in ('+@questionList+')
) pvt
order by user_id'

exec sp_executesql @SQL

Best Answer

Related Solutions

Sql-server – Dynamic Number of Columns in Pivot Table

Sql-server – Dynamic SELECT and place result in variable columns

Related Question