Post reply

SQL code

  • April 12, 2017 at 12:41 pm

    #322957

    ServerName    type    type_desc    DBname    physical_name    state    state_desc    Datetime
    A        0    ROWS        Db1    E:\Db1.mdf    0    ONLINE        43:27.2
    A        0    ROWS        Db2    E:\Db2.mdf    0    ONLINE        43:27.2
    B        1    LOG        Db1    F:\Db1.ldf    0    ONLINE        43:27.2
    B        1    LOG        Db2    F:\Db2.ldf    0    ONLINE        43:27.2
    C        0    ROWS        Db3    E:\Db3.mdf    0    ONLINE        43:27.2
    C        1    LOG        Db3    F:\Db3.ldf    0    ONLINE        43:27.2
    D        0    ROWS        Db4    E:\Db4.mdf    0    ONLINE        43:27.2
    D        1    LOG        Db4    F:\Db4.ldf    0    ONLINE        43:27.2
    E        0    ROWS        Db5    E:\Db5.mdf    0    ONLINE        43:27.2
    E        1    LOG        Db5    F:\Db5.ldf    0    ONLINE        43:27.2
    E        0    ROWS        Db6    E:\Db6.mdf    0    ONLINE        43:27.2
    E        1    LOG        Db6    F:\Db6.ldf    0    ONLINE        43:27.2
    E        0    ROWS        Db7    E:\Db7.mdf    0    ONLINE        43:27.2
    E        1    LOG        Db8    E:\Db8.ldf    0    ONLINE        43:27.2

    I need to represent the above table in this way in a select statement.
    Any number of physical_name values for one DBName the files(.mdf and .ldf) will come in the same line for one DBName column value.If .mdf is missing for Dbname then it an empty value under physical_name1,similarly if .ldf is missing under Dbname then it will be a missing value under physical_name2 column.

    SN    Type     Dbname    physical_name1    physical_name2    state    state_desc    Datetime
    A     0                     Db1     E:\Db1.mdf          F:\Db1.ldf                0     ONLINE         43:27.2
    B     0                     Db2     E:\Db2.mdf           F:\Db2.ldf               0     ONLINE         43:27.2
    C     0    
    ..
    .
    .
    ..
    ..
    E     0                     Db5     E:\Db5.mdf             F:\Db5.ldf               0     ONLINE         43:27.2
    E     0                     Db6     E:\Db6.mdf             F:\Db6.ldf               0     ONLINE         43:27.2
    E     0                     Db7     E:\Db7.mdf                                            0     ONLINE         43:27.2
    E     0                     Db8                                     E:\Db8.ldf               0     ONLINE         43:27.2

  • April 12, 2017 at 2:22 pm

    #1937938

    sqlnewbie17 - Wednesday, April 12, 2017 12:41 PM

    ServerName    type    type_desc    DBname    physical_name    state    state_desc    Datetime
    A        0    ROWS        Db1    E:\Db1.mdf    0    ONLINE        43:27.2
    A        0    ROWS        Db2    E:\Db2.mdf    0    ONLINE        43:27.2
    B        1    LOG        Db1    F:\Db1.ldf    0    ONLINE        43:27.2
    B        1    LOG        Db2    F:\Db2.ldf    0    ONLINE        43:27.2
    C        0    ROWS        Db3    E:\Db3.mdf    0    ONLINE        43:27.2
    C        1    LOG        Db3    F:\Db3.ldf    0    ONLINE        43:27.2
    D        0    ROWS        Db4    E:\Db4.mdf    0    ONLINE        43:27.2
    D        1    LOG        Db4    F:\Db4.ldf    0    ONLINE        43:27.2
    E        0    ROWS        Db5    E:\Db5.mdf    0    ONLINE        43:27.2
    E        1    LOG        Db5    F:\Db5.ldf    0    ONLINE        43:27.2
    E        0    ROWS        Db6    E:\Db6.mdf    0    ONLINE        43:27.2
    E        1    LOG        Db6    F:\Db6.ldf    0    ONLINE        43:27.2
    E        0    ROWS        Db7    E:\Db7.mdf    0    ONLINE        43:27.2
    E        1    LOG        Db8    E:\Db8.ldf    0    ONLINE        43:27.2

    I need to represent the above table in this way in a select statement.
    Any number of physical_name values for one DBName the files(.mdf and .ldf) will come in the same line for one DBName column value.If .mdf is missing for Dbname then it an empty value under physical_name1,similarly if .ldf is missing under Dbname then it will be a missing value under physical_name2 column.

    SN    Type     Dbname    physical_name1    physical_name2    state    state_desc    Datetime
    A     0                     Db1     E:\Db1.mdf          F:\Db1.ldf                0     ONLINE         43:27.2
    B     0                     Db2     E:\Db2.mdf           F:\Db2.ldf               0     ONLINE         43:27.2
    C     0    
    ..
    .
    .
    ..
    ..
    E     0                     Db5     E:\Db5.mdf             F:\Db5.ldf               0     ONLINE         43:27.2
    E     0                     Db6     E:\Db6.mdf             F:\Db6.ldf               0     ONLINE         43:27.2
    E     0                     Db7     E:\Db7.mdf                                            0     ONLINE         43:27.2
    E     0                     Db8                                     E:\Db8.ldf               0     ONLINE         43:27.2

    Please post DDL and sample data in a consumable format. Read the links in my signature to know how to do that.
    Or post the query that you're using right now if those are system views.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • April 12, 2017 at 2:24 pm

    #1937939

    Hit google and look for 'dynamic pivot', that should be enough to get you started.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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