cross tab or derived table

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • mirth
    #1

    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.
    Tags: None
    • Simon Hayes
      #2
      Re: cross tab or derived table


      "mirth" <contactmirth@y ahoo.co.uk> wrote in message
      news:7869c14.04 05310459.8b8f53 3@posting.googl e.com...[color=blue]
      > 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.[/color]

      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


        Comment

        • John Bell
          #3
          Re: cross tab or derived table

          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:
          404 Not Found
          http://www.winnetmag.com/SQLServer/Article/ArticleID/15608/15608.html



          John

          "mirth" <contactmirth@y ahoo.co.uk> wrote in message
          news:7869c14.04 05310459.8b8f53 3@posting.googl e.com...[color=blue]
          > 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.[/color]


            Comment

            • mirth
              #4
              Re: cross tab or derived table

              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" <jbellnewsposts @hotmail.com> wrote in message news:<RiOuc.810 $Z12.6730874@ne ws-text.cableinet. net>...[color=blue]
              > 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" <contactmirth@y ahoo.co.uk> wrote in message
              > news:7869c14.04 05310459.8b8f53 3@posting.googl e.com...[color=green]
              > > 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.[/color][/color]

                Comment

                • Gary Watson
                  #5
                  Re: cross tab or derived table


                  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!

                    Comment

                    Previous template Next
                    Working...