cross tab or derived table

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."mirth" <contactmirth@.yahoo.co.uk> wrote in message
news:7869c14.0405310459.8b8f533@.posting.google.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.

I must admit that I don't really follow your description. Perhaps you could
post a CREATE TABLE statement for your table, plus INSERT statements for
some sample data, and also the expected result set - that would be much
clearer than a narrative.

Simon|||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/...5608/15608.html

John

"mirth" <contactmirth@.yahoo.co.uk> wrote in message
news:7869c14.0405310459.8b8f533@.posting.google.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.|||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 #SummaryTempTable
CREATE TABLE #SummaryTempTable (
[GLD_ACCTNG_PER] INT,
[External Sales] decimal,
[Internal Sales] decimal,
[Mark Up] decimal
)

INSERT INTO #SummaryTempTable
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" <jbellnewsposts@.hotmail.com> wrote in message news:<RiOuc.810$Z12.6730874@.news-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/...5608/15608.html
>
> John
> "mirth" <contactmirth@.yahoo.co.uk> wrote in message
> news:7869c14.0405310459.8b8f533@.posting.google.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.|||Thanks guys but I sussed it. I went back to the drawing board and redone
the #temp table differently.

Appreciate the help though.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!