query Help

  • Hi

    I ahve table called Server with different versions. I have duplicate rows because of Verisons. So i want to change my table structure to single row with all versions.

    I have of thousandsof rwos like this.

    I have values like below

    | ServerName | sybase | MQ || Citrixfarm | oracle | UDBW || IHS || WAS |

    ----------------------------------------------------------------------------------------------------------------------

    | ABC1234 | 6.2.5.0 || nULL || nULL || nULL|| || 6.2.5.0 || nULL | 2.7.6.3

    | ABC1234 | nULL || 3.8.88.9 || nULL || 5.6.7.8 || NULL || nULL | NULL

    i NEED THE OUPPUT LKE BELOW.

    | ServerName | sybase | MQ || Citrixfarm | oracle | UDBW || IHS || WAS |

    ----------------------------------------------------------------------------------------------------------------------

    | ABC1234 | 6.2.5.0 || 3.8.88.9 || nULL || 5.6.7.8 || 6.2.5.0 || nULL || 2.7.6.3

  • Your table is really structured like that? What happens if you get more software packages? Does the table structure change? IF y

    I think storing information using this structure/pattern is bound to fail. I worked on data like this every day for six months, and it was a nightmare.

    Why not use a different table design?

    Server===(1,M)---InstalledSoftware---(M,1)---SoftwareVersion---(M,1)---SoftwareTitle

    then you can add servers, versions, titles all you want. If you want a crosstab, use SSRS or do a PIVOT.

  • pietlinden (12/12/2013)


    Your table is really structured like that? What happens if you get more software packages? Does the table structure change? IF y

    I think storing information using this structure/pattern is bound to fail. I worked on data like this every day for six months, and it was a nightmare.

    Why not use a different table design?

    Server===(1,M)---InstalledSoftware---(M,1)---SoftwareVersion---(M,1)---SoftwareTitle

    then you can add servers, versions, titles all you want. If you want a crosstab, use SSRS or do a PIVOT.

    +1 +1 +1


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • This would work for the data you've given:

    USE [tempdb]

    --== SAMPLE DATA ==--

    IF OBJECT_ID('tempdb..#Servers') IS NOT NULL DROP TABLE #Servers

    CREATE TABLE #Servers

    (

    ServerNameVarchar(25) NULL,

    sybaseVarchar(25) NULL,

    MQVarchar(25) NULL,

    CitrixfarmVarchar(25) NULL,

    oracleVarchar(25) NULL,

    UDBWVarchar(25) NULL,

    IHSVarchar(25) NULL,

    WASVarchar(25) NULL

    )

    INSERT #Servers

    (ServerName, sybase, MQ, Citrixfarm, oracle, UDBW, IHS, WAS)

    VALUES

    ('ABC1234', '6.2.5.0', NULL, NULL, NULL, '6.2.5.0', NULL, '2.7.6.3')

    INSERT #Servers

    (ServerName, sybase, MQ, Citrixfarm, oracle, UDBW, IHS, WAS)

    VALUES

    ('ABC1234', NULL, '3.8.88.9', NULL, '5.6.7.8', NULL, NULL, NULL)

    SELECT * FROM #Servers

    --== SUGGESTED SOLUTION ==--

    SELECT ServerName,

    sybase = MAX(sybase),

    MQ = MAX(MQ),

    Citrixfarm = MAX(Citrixfarm),

    oracle = MAX(oracle),

    UDBW = MAX(UDBW),

    IHS = MAX(IHS),

    WAS = MAX(WAS)

    FROM #Servers

    GROUP BY ServerName

    ORDER BY ServerName

  • dwain.c (12/13/2013)


    pietlinden (12/12/2013)


    Your table is really structured like that? What happens if you get more software packages? Does the table structure change? IF y

    I think storing information using this structure/pattern is bound to fail. I worked on data like this every day for six months, and it was a nightmare.

    Why not use a different table design?

    Server===(1,M)---InstalledSoftware---(M,1)---SoftwareVersion---(M,1)---SoftwareTitle

    then you can add servers, versions, titles all you want. If you want a crosstab, use SSRS or do a PIVOT.

    +1 +1 +1

    +1 also. In the original design, you'd need to keep adding columns forever. Properly structuring it will help you so very much in the end. The pain you'll avoid down the line by restructuring it now will be very much worth the effort.

  • pietlinden (12/12/2013)


    Your table is really structured like that? What happens if you get more software packages? Does the table structure change? IF y

    I think storing information using this structure/pattern is bound to fail. I worked on data like this every day for six months, and it was a nightmare.

    Why not use a different table design?

    Server===(1,M)---InstalledSoftware---(M,1)---SoftwareVersion---(M,1)---SoftwareTitle

    then you can add servers, versions, titles all you want. If you want a crosstab, use SSRS or do a PIVOT.

    +1. Normalise this mess.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • hi thank you very much for your inputs. Ireally appreciate your response.

    Finaly i get rid of this mess by using PIVOT table in slq server as below.

    select ServerName,

    SYBASE, MQ, [CITRIX FARM],

    Oracle, [UDB DW],CITRIX, IHS, WAS, IIS, UDB,[.NET],[SQL SERVER]

    from

    (

    SELECT

    ServerName,

    Adder_Name,

    Adder_ver

    FROM tableA

    ) d

    pivot

    (

    Max(Adder_ver)

    for Adder_Name in (SYBASE, MQ, [CITRIX FARM],

    Oracle, [UDB DW],CITRIX, IHS, WAS, IIS, UDB,[.NET],[SQL SERVER])

    ) piv

Viewing 7 posts - 1 through 6 (of 6 total)

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