0

I have a dataset that looks like the following: 

CDATE      | TRANSACTIONS | TOTALBALANCE | TRANSDATE
2003-01-15 | 30           | 5000         | 4-2007
2003-01-15 | 30           | 5000         | 4-2007
2003-02-15 | 40           | 4000         | 4-2007
2003-03-15 | 20           | 5500         | 4-2007
2003-03-15 | 15           | 5500         | 5-2007
2003-03-15 | 15           | 5500         | 6-2007
And so on

I'd like to query this so I get something like this: 

CDATE      | BALANCE | 04-2007 | 05-2007 | 06-2007 | TRANS TOT
2003-01-15 | 5000    | 60      | 0       | 0       | 60 
2003-02-15 | 4000    | 40      | 0       | 0       | 40
2003-03-15 | 5500    | 20      | 15      | 15      | 50

Essentially I wish to group the 'CDATE' together with their totals (the total column is always the same across CDates because of the way it was set up) followed by the transactions grouped by date and a total.

Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
Patrick Schomburg
  • 2,494
  • 1
  • 18
  • 46
  • this is called a pivot table and is DBMS specific so what database and version are you using? it also depends on how many trans dates you can have. If variable, then you need a Dynamic Pivot and several examples exist on Stack already. – xQbert Nov 29 '16 at 19:46
  • I'm on slq 2008 R2. The trans dates would indeed be variable. – Patrick Schomburg Nov 29 '16 at 19:52
  • I refer you to this prior question on stack then: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query bluefeet's example has been widely used. – xQbert Nov 29 '16 at 19:53
  • What syntax do I use if the data already came from a query? I tried `@dataset = STUFF ...` but it returns an error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS" – Patrick Schomburg Nov 29 '16 at 20:07
  • Write your dataset to a temp table or use a common table expression (CTE) to generate the data set and build from the CTE or temp table. Then it's a matter of following the example, you could even call the cte temp and then do `SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.TransDate) FROM temp c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')` you may need to add a group by grouping set to get a running total by CDate in your base query however. – xQbert Nov 29 '16 at 20:13
  • I'd play around with it for a while, if you're stuck ask another questions specific to the problem you're facing. with it being specific to your task and showing the effort you've tried, you'll likely get a better answer than a closed question as a dup. – xQbert Nov 29 '16 at 20:14
  • I understand, thank you. – Patrick Schomburg Nov 29 '16 at 20:14

0 Answers0