Post reply

Parameters in columns

  • April 16, 2008 at 4:57 am

    #189430

    I already know this is a daft question but Im going to carry on regardless 🙂

    I have for example a table that looks like....

    TITLE TOT_All_RECS TOT_SEN TOT_TRAV TOT_LAC

    Q1 100 333 4565 3433

    Q2 6 4 55 56

    Q3 35 3 0 35

    And Im using this table in cross joins... e.g. (FOR ALL_RECS)

    SELECT a.TOT_ALL_RECS AS 'Q1_ALL_RECS',

    b.TOT_ALL_RECS AS 'Q2_ALL_RECS,

    c.TOT_ALL_RECS AS Q3_ALL_RECS,

    FROM dbo.MyTable a

    CROSS JOIN dbo.MyTable b

    CROSS JOIN dbo.MyTable c

    WHERE a.Title = 'Q1'

    AND b.Title = 'Q2'

    AND c.Title = 'Q03'

    so eventually you get

    Q1_ALL_RECS Q2_ALL_RECS Q3_ALL_RECS

    100 6 35

    Q1_SEN Q2_SEN Q3_SEN

    333 4 3

    Etc

    Rather than having to do a cross join for each column is there a way that you could use some kind of parameter? Such as

    TOT_@Service_Type

    So If you chose ALL_RECS it would grab the columns with ALL_RECS in the title

  • April 16, 2008 at 5:51 am

    #803008

    Ive sorted it. Ive unioned all the SQL together and added an extra column which shows the service the row belongs too.

    Debbie

  • April 16, 2008 at 1:29 pm

    #803292

    You might also want to take a look at the Pivot command in Books Online. That might do what you need.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • April 18, 2008 at 1:55 am

    #804136

    I starteed looking at pivots for something else a while ago but i got a little lost. I will certianly have another go now Im abit more familiar with SQL Server 2005

    Thanks

    Debbie

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply