Ok. thanks guys. I have already read the below website. The current
status is something like this. There will be more columns in the final
table but it will give you an idea. It's very much a work in progress.
DROP TABLE #SummaryTempTab le
CREATE TABLE #SummaryTempTab le (
[GLD_ACCTNG_PER] INT,
[External Sales] decimal,
[Internal Sales] decimal,
[Mark Up] decimal
)
INSERT INTO #SummaryTempTab le
SELECT GLD_ACCTNG_PER,
SUM(CASE WHEN (ACT_GL_NO between '4000' and '4399') THEN GLD_Total
ELSE 0 END) as 'External Sales',
SUM(CASE WHEN ((ACT_GL_NO between '4400' and '4499') and (ACT_GL_NO
<> '4400')) THEN GLD_Total ELSE 0 END) as 'Internal Sales',
SUM(CASE WHEN (ACT_GL_NO = '4400') THEN GLD_Total ELSE 0 END) as
'Mark Up'
FROM FinancePeriod
WHERE ([coid] = 'SPL') AND (GLD_SSN_BRH = 'CUM') AND (GLD_ACCTNG_PER
like '2004%') AND ACT_GL_NO BETWEEN 4000 AND 9999
GROUP BY GLD_ACCTNG_PER
The select statement gives data like this....
Period External Sales Internal Sales Mark Up
200301 -1287015 -4006 -202543
200302 -1771901 -8053 -350049
200303 -1831843 -9412 -215212
200304 -1193717 -1824 -103048
200305 -1450486 -5807 -242358
I need the output to look like this....
200301 200302 200303 200304 200305
External Sales -1287015 -1771901 -1831843 -1193717 -1450486
Internal Sales -4006 -8053 -9412 -1824 -5807
Mark Up -202543 -350049 -215212 -103048 -242358
I hope this helps explain a little better what I'm trying to do.
"John Bell" <jb************ @hotmail.com> wrote in message news:<Ri******* ************@ne ws-text.cableinet. net>...
Hi
As Simon asked!! Please supply DDL (Create table) statements and example
data (as insert statements) with the required output from that data.
You may want to look at previous posts regarding crosstab queries and
Itzik's article at:
http://www.winnetmag.com/SQLServer/A...608/15608.html
John
"mirth" <co**********@y ahoo.co.uk> wrote in message
news:78******** *************** *@posting.googl e.com... Hi all,
I have a table in this format
colname1 colname2 colname3
col1data1 col2data1 col3data1
col1data2 col2data2 col3data2
col1data3 col2data3 col3data3
col1data4 col2data4 col3data4
I want to display it in this format
colname1 col1data1 col1data2 col1data3 col1data4
colname2 col2data1 col2data2 col2data3 col2data4
colname3 col3data1 col3data2 col3data3 col3data4
Basically rotate it through 90 degrees clockwise and flip it over :)
I'm pretty sure this is done by using a crosstab query and or a
derived table or temp table. The problem is I use a crosstab query to
get the original data into the first format. I've been struggling
trying to get the ouptput into the second format for over a day now
and just can't seem to get it to work. Can anyone give me any pointers
on the general solution to this?
I hope this makes sense. Thanks for the help.
