locked
Help need in solving a Query RRS feed

  • Question

  • TABLE

    Node   node1   node2   node3      param              value

    abc         null         bcd         cde         azimuth                321

    acd         null         bcd         cde         azimuthgen        421

    acd         null         bcd         cde         azimuthgen        221

    abc         null         bcd         feg         azimuth                234

    abc         null         bcd         cde         betcel                  231

    abc         null         bcd         cde         cetcom                521

    abc         def         null         yuh        getcel                  731

     

     

    OUTPUT

    Node   node1   node2   node3    azimuth  azimuthgen   betcel   cetcom  getcel

    abc         null         bcd         cde         321

    acd         null         bcd         cde                             421,221            

     abc        null         bcd         feg          234

    abc         null         bcd         cde                                                         231

    abc         null         bcd         cde                                                         521

    abc         def         null         yuh                                                                        731

     

     

    Using that table i need o/p as shown.

    Help me with query & any valuable suggestions.

     

    Thanks in Advance.

    Wednesday, October 13, 2010 3:07 AM

Answers

  • declare @t table 
    (
    	Node varchar(100), node1 varchar(100),  node2 varchar(100),
    	node3 varchar(100),   [param] varchar(100),       value int	
    )
    insert into @t 
    select 'abc',null,'bcd','cde','azimuth',321 union all
    select 'abc',null,'bcd','cde','azimuthgen',421 union all
    select 'abc',null,'bcd','cde','azimuthgen',221 union all
    select 'abc',null,'bcd','feg','azimuth',234 union all
    select 'abc',null,'bcd','cde','betcel',231 union all
    select 'abc',null,'bcd','cde','cetcom',521 union all
    select 'abc','def','null','yuh','getcel',731
    
    ;with cte as
    (
    	select distinct Node,Node1,Node2,Node3,[Param],Vals from @t t
    	cross apply (select stuff((select ',' + CAST(value AS varchar(20)) from @t where 
    	Node = t.Node and ( node1 = t.node1 or node1 is null) and ( node2= t.node2 or node2 is null) and node3 = t.node3 and [param] = t.[param]
    	for XML path('')),1,1,'') as Vals)t1
    )
    select * from 
    (
    	select *,ROW_NUMBER() over (Order by (select 1)) as rn from cte
    )p
    pivot(max(Vals) for [Param] in ([azimuth],[azimuthgen],[betcel],[cetcom],[getcel]))pvt
    
    • Marked as answer by DJ3001 Wednesday, October 13, 2010 2:21 PM
    Wednesday, October 13, 2010 3:22 AM
  • Try

    if OBJECT_ID('tempDB..#Temp','U') IS NOT NULL drop table #Temp
    create table #Temp (Node char(3), node1 char(3), node2 char(3), node3 char(3),  [param] varchar(20), value int)
    insert into #Temp
    select
    'abc',   null,   'bcd',   'cde',   'azimuth',    321
    union all select
    
    'acd',   null,   'bcd',   'cde',   'azimuthgen',  421
    union all select
    'acd',   null,   'bcd',   'cde',   'azimuthgen',  221
    union all select
    'abc',   null,   'bcd',   'feg',   'azimuth',    234
    union all select
    'abc',   null,   'bcd',   'cde',   'betcel',     231
    union all select
    'abc',   null,   'bcd',   'cde',   'cetcom',    521
    union all select
    'abc',   'def',   null,   'yuh',  'getcel',     731
    
    
    declare @Cols nvarchar(max), @SQL nvarchar(max)
    select @Cols = STUFF((select distinct ', ' + quotename([Param]) from #Temp order by ', ' + quotename([param]) for XML PATH('')),1,2,'')
    
    set @SQL = 'select * from (select Node, Node1, Node2, Node3,[Param], 
    STUFF((select '','' + cast(value as varchar(20)) from #Temp T1 
    where COALESCE(T1.Node,'''') = coalesce(T.Node,'''') and
    coalesce(T1.Node1,'''') = coalesce(T.Node1,'''') and
    coalesce(T1.Node2,'''') = coalesce(T.Node2,'''') and
    coalesce(T1.Node3,'''') = coalesce(T.Node3,'''') and
    coalesce(T1.[Param],'''') = coalesce(T.[Param],'''') FOR XML PATH('''')),1,1,'''') as [Values]
    
    from #Temp T 
    group by Node, node1, node2, node3, [Param]) src 
    PIVOT (max([Values]) FOR [Param] IN ('+ @Cols + ')) pvt ORDER BY Node1, Node3, Node, Node2'
    --print @SQL
    execute (@SQL)
    
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by DJ3001 Wednesday, October 13, 2010 2:21 PM
    Wednesday, October 13, 2010 3:46 AM
  • try this one -

    DECLARE @tab TABLE

    (

          Node VARCHAR(50),

          Node1 VARCHAR(50),

          Node2 VARCHAR(50),

          Node3 VARCHAR(50),

          Param VARCHAR(50),

          Value INT

    )

    INSERT INTO @tab (Node,Node1,Node2,Node3,Param,Value)

    SELECT 'abc',null,'bcd','cde','azimuth',321

    UNION ALL

    SELECT 'acd',null,'bcd','cde','azimuthgen',421

    UNION ALL

    SELECT 'acd',null,'bcd','cde','azimuthgen',221

    UNION ALL

    SELECT 'abc',null,'bcd','feg','azimuth',234

    UNION ALL

    SELECT 'abc',null,'bcd','cde','betcel',231

    UNION ALL

    SELECT 'abc',null,'bcd','cde','cetcom',521

    UNION ALL

    SELECT 'abc','def',null,'yuh','getcel',731

     

    ;with cte as

    (   

        SELECT  Node,

                      Node1,

                      Node2,

                      Node3,

                      Param,

                      STUFF(

                                  (SELECT ', ' +  CAST(Value AS VARCHAR(50))  as [text()]

                                   FROM @tab

                                   WHERE Param = t.Param

                                   AND ISNULL(Node1,'') = ISNULL(t.Node1,'')

                                   AND ISNULL(Node2,'') = ISNULL(t.Node2,'')

                                   AND ISNULL(Node3,'') = ISNULL(t.Node3,'')

                                   GROUP BY Param,Value  

                                   ORDER BY Value DESC     

                                   FOR XML PATH('')       

                                   ), 1, 2, ''

                              ) Value,

                              row_number() over (order by value asc) as rowid

          FROM @tab t   

    )    

     

    SELECT DISTINCT Node,

             Node1,

             Node2,

             Node3,

             [azimuth],

             [azimuthgen],  

             [betcel],  

             [cetcom], 

             [getcel]

    FROM (

                 SELECT Node,

                            Node1,

                            Node2,

                            Node3,

                            Param,

                            Value,

                            rowid

                 FROM cte

         )ps

    PIVOT

    (

       MAX(Value)

       FOR Param IN ([azimuth],[azimuthgen],[betcel],[cetcom],[getcel])

    )AS PVT

     

    Thanks,


    Kapil Khalas - Database Developer
    • Marked as answer by DJ3001 Wednesday, October 13, 2010 2:21 PM
    Wednesday, October 13, 2010 11:39 AM

All replies

  • declare @t table 
    (
    	Node varchar(100), node1 varchar(100),  node2 varchar(100),
    	node3 varchar(100),   [param] varchar(100),       value int	
    )
    insert into @t 
    select 'abc',null,'bcd','cde','azimuth',321 union all
    select 'abc',null,'bcd','cde','azimuthgen',421 union all
    select 'abc',null,'bcd','cde','azimuthgen',221 union all
    select 'abc',null,'bcd','feg','azimuth',234 union all
    select 'abc',null,'bcd','cde','betcel',231 union all
    select 'abc',null,'bcd','cde','cetcom',521 union all
    select 'abc','def','null','yuh','getcel',731
    
    ;with cte as
    (
    	select distinct Node,Node1,Node2,Node3,[Param],Vals from @t t
    	cross apply (select stuff((select ',' + CAST(value AS varchar(20)) from @t where 
    	Node = t.Node and ( node1 = t.node1 or node1 is null) and ( node2= t.node2 or node2 is null) and node3 = t.node3 and [param] = t.[param]
    	for XML path('')),1,1,'') as Vals)t1
    )
    select * from 
    (
    	select *,ROW_NUMBER() over (Order by (select 1)) as rn from cte
    )p
    pivot(max(Vals) for [Param] in ([azimuth],[azimuthgen],[betcel],[cetcom],[getcel]))pvt
    
    • Marked as answer by DJ3001 Wednesday, October 13, 2010 2:21 PM
    Wednesday, October 13, 2010 3:22 AM
  • Try

    if OBJECT_ID('tempDB..#Temp','U') IS NOT NULL drop table #Temp
    create table #Temp (Node char(3), node1 char(3), node2 char(3), node3 char(3),  [param] varchar(20), value int)
    insert into #Temp
    select
    'abc',   null,   'bcd',   'cde',   'azimuth',    321
    union all select
    
    'acd',   null,   'bcd',   'cde',   'azimuthgen',  421
    union all select
    'acd',   null,   'bcd',   'cde',   'azimuthgen',  221
    union all select
    'abc',   null,   'bcd',   'feg',   'azimuth',    234
    union all select
    'abc',   null,   'bcd',   'cde',   'betcel',     231
    union all select
    'abc',   null,   'bcd',   'cde',   'cetcom',    521
    union all select
    'abc',   'def',   null,   'yuh',  'getcel',     731
    
    
    declare @Cols nvarchar(max), @SQL nvarchar(max)
    select @Cols = STUFF((select distinct ', ' + quotename([Param]) from #Temp order by ', ' + quotename([param]) for XML PATH('')),1,2,'')
    
    set @SQL = 'select * from (select Node, Node1, Node2, Node3,[Param], 
    STUFF((select '','' + cast(value as varchar(20)) from #Temp T1 
    where COALESCE(T1.Node,'''') = coalesce(T.Node,'''') and
    coalesce(T1.Node1,'''') = coalesce(T.Node1,'''') and
    coalesce(T1.Node2,'''') = coalesce(T.Node2,'''') and
    coalesce(T1.Node3,'''') = coalesce(T.Node3,'''') and
    coalesce(T1.[Param],'''') = coalesce(T.[Param],'''') FOR XML PATH('''')),1,1,'''') as [Values]
    
    from #Temp T 
    group by Node, node1, node2, node3, [Param]) src 
    PIVOT (max([Values]) FOR [Param] IN ('+ @Cols + ')) pvt ORDER BY Node1, Node3, Node, Node2'
    --print @SQL
    execute (@SQL)
    
    


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by DJ3001 Wednesday, October 13, 2010 2:21 PM
    Wednesday, October 13, 2010 3:46 AM
  • here we go

    declare @t table
    (
     Node varchar(100), node1 varchar(100),  node2 varchar(100),
     node3 varchar(100),   [param] varchar(100),       value int 
    )
    insert into @t
    select 'abc',null,'bcd','cde','azimuth',321 union all
    select 'abc',null,'bcd','cde','azimuthgen',421 union all
    select 'abc',null,'bcd','cde','azimuthgen',221 union all
    select 'abc',null,'bcd','feg','azimuth',234 union all
    select 'abc',null,'bcd','cde','betcel',231 union all
    select 'abc',null,'bcd','cde','cetcom',521 union all
    select 'abc','def','null','yuh','getcel',731;

     

    select node,node1,node2,node3
     ,sum(case when param='azimuth' then value else 0 end) azimuth
     ,sum(case when param='azimuthgen' then value else 0 end) azimuthgen
     ,sum(case when param='betcel' then value else 0 end) betcel
     ,sum(case when param='cetcom' then value else 0 end) cetcom
     ,sum(case when param='getcel' then value else 0 end) getcel
    from @t
    group by  node,node1,node2,node3

    • Proposed as answer by Muhammad Abbas Wednesday, October 13, 2010 8:33 AM
    • Marked as answer by DJ3001 Wednesday, October 13, 2010 2:21 PM
    • Unmarked as answer by DJ3001 Wednesday, October 13, 2010 2:23 PM
    Wednesday, October 13, 2010 8:33 AM
  • try this one -

    DECLARE @tab TABLE

    (

          Node VARCHAR(50),

          Node1 VARCHAR(50),

          Node2 VARCHAR(50),

          Node3 VARCHAR(50),

          Param VARCHAR(50),

          Value INT

    )

    INSERT INTO @tab (Node,Node1,Node2,Node3,Param,Value)

    SELECT 'abc',null,'bcd','cde','azimuth',321

    UNION ALL

    SELECT 'acd',null,'bcd','cde','azimuthgen',421

    UNION ALL

    SELECT 'acd',null,'bcd','cde','azimuthgen',221

    UNION ALL

    SELECT 'abc',null,'bcd','feg','azimuth',234

    UNION ALL

    SELECT 'abc',null,'bcd','cde','betcel',231

    UNION ALL

    SELECT 'abc',null,'bcd','cde','cetcom',521

    UNION ALL

    SELECT 'abc','def',null,'yuh','getcel',731

     

    ;with cte as

    (   

        SELECT  Node,

                      Node1,

                      Node2,

                      Node3,

                      Param,

                      STUFF(

                                  (SELECT ', ' +  CAST(Value AS VARCHAR(50))  as [text()]

                                   FROM @tab

                                   WHERE Param = t.Param

                                   AND ISNULL(Node1,'') = ISNULL(t.Node1,'')

                                   AND ISNULL(Node2,'') = ISNULL(t.Node2,'')

                                   AND ISNULL(Node3,'') = ISNULL(t.Node3,'')

                                   GROUP BY Param,Value  

                                   ORDER BY Value DESC     

                                   FOR XML PATH('')       

                                   ), 1, 2, ''

                              ) Value,

                              row_number() over (order by value asc) as rowid

          FROM @tab t   

    )    

     

    SELECT DISTINCT Node,

             Node1,

             Node2,

             Node3,

             [azimuth],

             [azimuthgen],  

             [betcel],  

             [cetcom], 

             [getcel]

    FROM (

                 SELECT Node,

                            Node1,

                            Node2,

                            Node3,

                            Param,

                            Value,

                            rowid

                 FROM cte

         )ps

    PIVOT

    (

       MAX(Value)

       FOR Param IN ([azimuth],[azimuthgen],[betcel],[cetcom],[getcel])

    )AS PVT

     

    Thanks,


    Kapil Khalas - Database Developer
    • Marked as answer by DJ3001 Wednesday, October 13, 2010 2:21 PM
    Wednesday, October 13, 2010 11:39 AM
  • Thanks for all your replies.

    It works for me.

     

    Wednesday, October 13, 2010 2:21 PM