none
Converting columns into a single row RRS feed

  • Question

  • Hi All.

    I have a table with has host information and another table with the disk space that each host has. The issue I have is that some hosts might have 2 drives, some might have 5 drives. I would like to know if its possible to have the drive information transformed from column based into a row so that foe each host, I can have a single column that has capacity information.

    Tuesday, June 18, 2019 6:07 PM

All replies

  • Hi MrFlinstone,

    The rules of engagement on this forum are very simple, while asking a question you shall provide the following:

    1. Queries to CREATE your table(s) including indexes, aka DDL.
    2. Queries to INSERT sample data, aka DML.
    3. The desired result given the sample, as text or image of excel for example.
    4. A short description of the business rules, and how you got 1-2 of the results.
    5. Which version of SQL Server you are using (this will help to fit the query to your version).



    Tuesday, June 18, 2019 8:26 PM
  • Hi MrFlinstone,

    As you did not provide any table,  I can only create tables as per my understanding. If it is not what you want,  please share us your table structure and some sample data along with your expected result.

    Really hope that could help.

    IF OBJECT_ID('tbl1') IS NOT NULL drop table  tbl1
    go 
    create table tbl1 ( 
    ID INT NOT NULL,
    name varchar(20),
    gender varchar(10),
    age int)
    insert into tbl1 values
    (1,'ABC','Female',15),
    (2,'DEF','Male',20),
    (3,'GHI','Male',21)
    
    IF OBJECT_ID('tbl2') IS NOT NULL drop table  tbl2
    go 
    create table tbl2 (
    name varchar(20),
    disk_space int) 
    
    insert into tbl2 values
    ('DEF',5),
    ('ABC',3),
    ('GHI',2)
    
    ----My first understanding: 
    declare @name varchar(20)
    declare @disk_space int
    declare @sql varchar(max)=''
    declare @sqlstring varchar(max)
    
    select @sqlstring= isnull(@sqlstring+',','')+
    ' max(case when name='''+name+'''then name else null end )name
     ,max(case when name='''+ name+'''then disk_space else null end)disk_space '
    from tbl2
    set @sql='select'+@sqlstring + 'from tbl2'
    print @sql
    exec(@sql)
    
    /*
    select max(case when name='DEF'then name else null end )name
     ,max(case when name='DEF'then disk_space else null end)disk_space , max(case when name='ABC'then name else null end )name
     ,max(case when name='ABC'then disk_space else null end)disk_space , max(case when name='GHI'then name else null end )name
     ,max(case when name='GHI'then disk_space else null end)disk_space from tbl2
    
    
    name  disk_space  name    disk_space  name    disk_space
    ----- ----------- ------- ----------- ------- -----------
    DEF   5           ABC     3           GHI     2
    */
    
    ----My second understanding: 
    select a.*,b.disk_space from 
    tbl1 a 
    join tbl2 b 
    on a.name=b.name
    
    /*
    ID   name   gender     age         disk_space
    ---- ------ ---------- ----------- -----------
    1    ABC    Female     15          3
    2    DEF    Male       20          5
    3    GHI    Male       21          2
    */

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 19, 2019 3:30 AM
  • CREATE  TABLE  #mable(mid INT, token nvarchar(16))

    INSERT INTO #mable VALUES (0, 'foo')
    INSERT INTO #mable VALUES(0, 'goo')
    INSERT INTO #mable VALUES(1, 'hoo')
    INSERT INTO #mable VALUES(1, 'moo')

    SELECT m1.mid,
           ( SELECT m2.token + ','
               FROM #mable m2
              WHERE m2.mid = m1.mid
              ORDER BY token
                FOR XML PATH('') ) AS token
      FROM #mable m1
     GROUP BY m1.mid ;

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, June 19, 2019 5:09 AM
    Answerer