How do you create a crosstab query?

• SQLUser-308930

  Old Hand

  Points: 312

  More actions

  March 26, 2009 at 2:06 pm

  #71997

  I have a table with 3 columns. Column 1 is the part number, column 2 is the qty and column 3 is the date. A part number can be listed many times thus the unique keys are the part number and date.

  Here is an example of what the table would look like:

  Part A qty 10 03/25/2009

  Part A qty 12 02/18/2009

  Part B qty 2 03/15/2009

  Part B qty 5 03/01/2009

  My output needs to look like this:

  02/18/2009 03/01/2009 03/15/2009 03/25/2009

  Part A qty 12 qty 10

  Part B qty 5 qty 2

  Do I use a crosstab query to get the desired output? If so, how would I create that?

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736642

  More actions

  March 26, 2009 at 2:41 pm

  #967057

  http://www.sqlservercentral.com/scripts/Miscellaneous/30931/

  Read in here about cross tabs.

  You do need a crosstab, though if there are potentially lots of entries you are going to have an ugly query and potentially a report that doesn't read well.

• steve dassin

  SSCrazy

  Points: 2396

  More actions

  March 26, 2009 at 5:41 pm

  #967156

  Steve Jones - Editor (3/26/2009)

  ---

  http://www.sqlservercentral.com/scripts/Miscellaneous/30931/

  Read in here about cross tabs.

  You do need a crosstab, though if there are potentially lots of entries you are going to have an ugly query and potentially a report that doesn't read well.

  I'm tempted to make Rac open source 🙂

  www.beyondsql.blogspot.com

• arun.sas

  SSChampion

  Points: 11831

  More actions

  March 26, 2009 at 9:14 pm

  #967190

  Hi dub,

  You need the output like this?

  PART(Col1)02/18/2009(Col2)03/01/2009(Col3)03/15/2009(Col4)03/25/2009(Col5)

  Part A qty 12 qty 10qty 0qty 0qty 0

  Part B qty 5 qty 2qty 0qty 0qty 0

  ARUN SAS

• arun.sas

  SSChampion

  Points: 11831

  More actions

  March 26, 2009 at 9:37 pm

  #967192

  HI,

  Try this code

  create table #abc

  (

  part varchar(2),

  qty int,

  date datetime

  )

  insert into #abc values ('A',10,'2009-01-01')

  insert into #abc values ('A',20,'2009-01-02')

  insert into #abc values ('A',28,'2009-01-03')

  insert into #abc values ('B',10,'2009-01-01')

  insert into #abc values ('B',28,'2009-01-02')

  insert into #abc values ('B',20,'2009-01-03')

  select part,

  sum(case when date = '2009-01-01' then qty else 0.00 end) date1,

  sum(case when date = '2009-01-02' then qty else 0.00 end) date2,

  sum(case when date = '2009-01-03' then qty else 0.00 end) date3

  from #abc

  group by part

  RESULT

  partdate1date2date3

  A10.0020.0028.00

  B10.0028.0020.00

  ARUN SAS