May 18, 2012 at 10:43 am
I have the following sample data:
create Table dailyLog(
logId int,
name varchar(100),
date date,
descr varchar(150),
amt money,
count int,
cmt char(255),
upd char(100)
)
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(8,'name1','2012-05-01', 'food1', 300, 4,'','user1-2012-01-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(8,'name1','2012-05-01', 'game1', 235.50, 12,'','user1-2012-01-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(8,'name1','2012-05-01', 'food2', 1555.75, 45,'','user1-2012-01-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(8,'name1','2012-05-01', 'tryout', 0, 0,'','user1-2012-05-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(8,'name1','2012-05-01', 'newItem', 756.55, 31,'','user1-2012-01-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(9,'nextName','2012-03-11', 'newItem', 1756.55, 231,'','user1-2012-01-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(9,'nextName','2012-03-11', 'food1', 1234.55, 362,'','user2-2012-01-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(9,'nextName','2012-03-11', 'again', 5631, 231,'','user2-2012-01-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(9,'nextName','2012-03-11', 'whatever', 900, 40,'','user1-2012-01-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(9,'nextName','2012-03-11', 'total', 12600.73, 1231,'','user1-2012-01-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(9,'anotherName','2012-05-10', 'whatever', 12900, 340,'','user1-2012-01-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(9,'anotherName','2012-05-10', 'cash', 467.99, 25,'','user1-2012-01-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(9,'anotherName','2012-05-10', 'credit', 784.23, 370,'','user1-2012-01-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(9,'anotherName','2012-05-10', 'newcredit', 3900, 840,'','user1-2012-01-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(8,'name1','2012-05-03', 'tryout', 150, 10,'','user1-2012-05-01');
Insert into dailyLog (logid, name, date, descr, amt, count, cmt, upd) values(8,'name1','2012-05-03', 'newItem', 1745.21, 211,'','user1-2012-05-01');
I am looking to create the following output: converting rows to columns :
Name Date food1Amt food1Cnt game1Amt game1Cnt tryoutAmt tryOutCnt NewItemamt NewItemCnt WhateverAmt WhateverCnt TotalAmt TotalCnt TryoutAmt TryoutCnt
Name1 012-05-01 300 4 235.5 12 0 0 756.55 31
what is the best way to get this data out ?
cross tab query or ?
The result set will be available via a downloadable excel link: results in a datatable and converted to excel.
Thanks,
May 18, 2012 at 11:10 am
Try something like that:
with
CTE_Amt as
(
select
logId,
name,
[date],
[food1],
[game1],
[tryout],
[newitem],
[whatever],
[total]
from (select descr, amt, logid, name, [date] from dailyLog) as SourceTable
pivot (max(amt) for descr in ([food1], [game1], [tryout],
[newitem], [whatever], [total])) as PivotTable
),
CTE_Cnt as
(
select
logId,
[food1],
[game1],
[tryout],
[newitem],
[whatever],
[total]
from (select descr, [count], logid, name, [date] from dailyLog) as SourceTable
pivot (max([count]) for descr in ([food1], [game1], [tryout],
[newitem], [whatever], [total])) as PivotTable
)
select
a.name,
a.date,
a.food1 as Food1Amt,
c.food1 as Food1Cnt,
a.game1 as Game1Amt,
c.game1 as Game1Cnt,
a.tryout as TryoutAmd,
c.tryout as TryoutCnt,
a.newitem as NewitemAmt,
c.newitem as NewitemCnt,
a.whatever as WhateverAmt,
c.whatever as WhateverCnt,
a.total as TotalAmt,
c.total as TotalCnt
from CTE_Amt as a
join CTE_Cnt as c on c.logId = a.logId
Hope this helps.
May 18, 2012 at 12:54 pm
The problem I have is:
descr
Amt
Cnt
are variables and can go upto 15 also. I do not have control on these fields as these are configurable by users.
May 18, 2012 at 2:41 pm
CELKO (5/18/2012)
Learn RDBMS and do this right.
what is the "right" way to do this?
there seems to be many ways of doing this
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 18, 2012 at 4:54 pm
CELKO (5/18/2012)
Everything is completely wrong.
No it's not. It's an EAV table and they do happen for some good reasons sometimes. The fact that the PK is missing is just an artifact of posting DDL. Most people forget it.
Lighten up, Joe. Being so testy all the time will make you go bald. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2012 at 5:29 pm
chandrika5 (5/18/2012)
The problem I have is:descr
Amt
Cnt
are variables and can go upto 15 also. I do not have control on these fields as these are configurable by users.
Ignoring our local wild dog with the dripping foam...
Can you explain this problem in a bit more detail? It sounds like what you're going to need is a dynamically generated pivot.
The problem with those is that a lot of front ends will require non-mutating metadata as to what they expect from the results of this query. What's the final intent for what you're currently attempting to do in SQL?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
May 21, 2012 at 7:20 am
There is a PK and everything is RIGHT. Thanks for your rude free advise: Celko. Hope
somebody out there will find your books useful.
I know my job and *never* expect others to do this for me.
Yes to others for suggestions, I know how to get such a report out.
This thread is closed from my side.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply