Help need in solving a Query

All replies

• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  Thanks for all your replies.

  It works for me.

   

  Wednesday, October 13, 2010 2:21 PM