Convert column values as column row RSS

• • Reply
  

  vishalvl

  Member

  62 Points

  51 Posts

  Convert column values as column row

  Feb 23, 2012 04:34 AM|LINK

  Hi , I have a query which return the data like in sql server
  
  param_value            display_value
  64.60.231.79              BROADCAST_2
  0                              GATEWAY_2
  64.60.231.72              IP_SUBNET_2
  2                              IP_TYPE
  255.255.255.248      NET_MASK_2
  74-78                      USABLE_RANGE_2
  64.60.231.73              WAN_IP_2
  
  Now i want the right side output to make as a column name
  
  BROADCAST_2  GATEWAY_2  IP_SUBNET_2  IP_TYPE  NET_MASK_2 USABLE_RANGE_2   WAN_IP_2
  
  and the param_values should be below that in a row.
  
  How can i do that
  
  PFB for my query
  
  select a.param_value , p.display_value from service_ext_data a , param_values p
  where view_id in (select top 1 view_id from service_view
  where subscr_no in (select subscr_no from ord_service_order where service_order_id in (
  select service_order_id from ord_service_order where order_id = 405767003
  and wp_process_id = 'Rcnfg-Circuit'
  )) ) 
  and p.param_id in(10268,10298,10354,10170,10485,10474,10270,10326,10261)
  and p.param_id = a.param_id
  
  
  can some one help me please just to inform you that my sql version is 2000 so pivot will not work

• • Reply
  

  sandeepmitta...

  Contributor

  6801 Points

  1059 Posts

  Re: Convert column values as column row

  Feb 23, 2012 04:56 AM|LINK

  Try this

  --EXEC [dbo].[usp_OM_CUST_NOTIFICATIONS_FXDEV_reconfig] 0
  ALTER PROCEDURE [dbo].[usp_OM_CUST_NOTIFICATIONS_FXDEV_reconfig] (
          @ORDER_ID INT = 0 )
  AS
  BEGIN
      SET NOCOUNT ON;
  	SELECT a.param_value , p.display_value INTO #IP_DETAIL
  	FROM service_ext_data a , param_values p
  	WHERE view_id IN (SELECT TOP 1 view_id FROM service_view
  		WHERE subscr_no IN (SELECT subscr_no FROM ord_service_order WHERE service_order_id IN (
  			SELECT service_order_id FROM ord_service_order WHERE order_id = 405767003
  			AND wp_process_id = 'Rcnfg-Circuit'
  			)
  		)
  	)
  	AND p.param_id in(10268,10298,10354,10170,10485,10474,10270,10326,10261)
  	AND p.param_id = a.param_id
  
  	
  	DECLARE @VALUE_LIST VARCHAR(1000), @QUERY NVARCHAR(1000)
  	
  	SELECT @VALUE_LIST = COALESCE(@VALUE_LIST + ',''' + param_value + '''', '''' + param_value + '''')
  	FROM #IP_DETAIL
  
  	CREATE TABLE #result (
  		[BROADCAST_2] VARCHAR(100)
  		, [GATEWAY_2] VARCHAR(100)
  		, [IP_SUBNET_2] VARCHAR(100)
  		, [IP_TYPE] VARCHAR(100)
  		, [NET_MASK_2] VARCHAR(100)
  		, [USABLE_RANGE_2] VARCHAR(100)
  		, [WAN_IP_2] VARCHAR(100)
  	)
  
  	SET @QUERY = 'INSERT INTO #result SELECT ' + @VALUE_LIST
  
  	EXEC sp_executesql @QUERY
  
  	SELECT * FROM #result
  	DROP TABLE #IP_DETAIL
  	DROP TABLE #result
  END

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Edited by sandeepmittal11 on Feb 23, 2012 05:02 AM
  • Marked as answer by vishalvl on Feb 23, 2012 05:02 AM
• • Reply
  

  vishalvl

  Member

  62 Points

  51 Posts

  Re: Convert column values as column row

  Feb 23, 2012 06:21 AM|LINK

  Just to add if my columns are constant but the output of the query may not have the same number of records, how can i handle that?

• • Reply
  

  sandeepmitta...

  Contributor

  6801 Points

  1059 Posts

  Re: Convert column values as column row

  Feb 23, 2012 06:26 AM|LINK

  --EXEC [dbo].[usp_OM_CUST_NOTIFICATIONS_FXDEV_reconfig] 0
  ALTER PROCEDURE [dbo].[usp_OM_CUST_NOTIFICATIONS_FXDEV_reconfig] (
          @ORDER_ID INT = 0 )
  AS
  BEGIN
      SET NOCOUNT ON;
  	SELECT  a.param_value , p.display_value INTO #IP_DETAIL
      FROM    service_ext_data a , param_values p
      WHERE   view_id IN (SELECT TOP 1 view_id FROM service_view
              WHERE subscr_no IN (SELECT subscr_no FROM ord_service_order WHERE service_order_id IN (
                      SELECT service_order_id FROM ord_service_order WHERE order_id = 405767003
                      AND wp_process_id = 'Rcnfg-Circuit'
                      )
              )
      )
      AND    p.param_id in(10268,10298,10354,10170,10485,10474,10270,10326,10261)
      AND    p.param_id = a.param_id
  
  	
  	DECLARE @COL_LIST VARCHAR(1000), @VALUE_LIST VARCHAR(1000), @QUERY NVARCHAR(1000)
  	
  	SELECT @COL_LIST = COALESCE(@COL_LIST + ',' + display_value, display_value)
  	FROM #IP_DETAIL
  	SELECT @VALUE_LIST = COALESCE(@VALUE_LIST + ',''' + param_value + '''', '''' + param_value + '''')
  	FROM #IP_DETAIL
  
  	CREATE TABLE #result (
  		[BROADCAST_2] VARCHAR(100)
  		, [GATEWAY_2] VARCHAR(100)
  		, [IP_SUBNET_2] VARCHAR(100)
  		, [IP_TYPE] VARCHAR(100)
  		, [NET_MASK_2] VARCHAR(100)
  		, [USABLE_RANGE_2] VARCHAR(100)
  		, [WAN_IP_2] VARCHAR(100)
  	)
  
  	SET @QUERY = 'INSERT INTO #result (' + @COL_LIST + ') SELECT ' + @VALUE_LIST
  
  	EXEC sp_executesql @QUERY
  
  	SELECT * FROM #result
  	DROP TABLE #IP_DETAIL
  	DROP TABLE #result
  END

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Marked as answer by vishalvl on Feb 23, 2012 06:30 AM
• • Reply
  

  vishalvl

  Member

  62 Points

  51 Posts

  Re: Convert column values as column row

  Feb 23, 2012 06:30 AM|LINK

  Thanks man , you are a champion in SQL server :)

• • Reply
  

  vishalvl

  Member

  62 Points

  51 Posts

  Re: Convert column values as column row

  Feb 23, 2012 06:46 AM|LINK

  Now if there are multiple records for every column then ?

• • Reply
  

  vishalvl

  Member

  62 Points

  51 Posts

  Re: Convert column values as column row

  Feb 23, 2012 06:49 AM|LINK

  Like i have this kind of output from the query

  13/HCGS/809421//PT	CUR_CIRCUIT_LEC1
  2	IP_TYPE
  64.60.231.79	BROADCAST_2
  13/HCGS/726260//PT	CUR_CIRCUIT_LEC1
  0	GATEWAY_2
  64.60.231.72	IP_SUBNET_2
  2	IP_TYPE
  255.255.255.248	NET_MASK_2
  74-78	USABLE_RANGE_2
  64.60.231.73	WAN_IP_2
  2	IP_TYPE

• • Reply
  

  vishalvl

  Member

  62 Points

  51 Posts

  Re: Convert column values as column row

  Feb 23, 2012 07:30 AM|LINK

  Just to add more details

  i have my output query as

  299215003	64.60.231.79	BROADCAST_2
  299215003	13/HCGS/726260//PT	CUR_CIRCUIT_LEC1
  299215003	64.60.231.72	IP_SUBNET_2
  299215003	2	IP_TYPE
  299215003	255.255.255.248	NET_MASK_2
  299215003	73-78	USABLE_RANGE_2
  323935003	64.60.231.79	BROADCAST_2
  323935003	13/HCGS/726260//PT	CUR_CIRCUIT_LEC1
  323935003	0	GATEWAY_2
  323935003	64.60.231.72	IP_SUBNET_2
  323935003	2	IP_TYPE
  323935003	255.255.255.248	NET_MASK_2
  323935003	74-78	USABLE_RANGE_2
  323935003	64.60.231.73	WAN_IP_2
  1052351003	13/HCGS/809421//PT	CUR_CIRCUIT_LEC1
  1052351003	2	IP_TYPE
  1052352003	2	IP_TYPE

  and i need my output as

  BROADCAST_2	CUR_CIRCUIT_LEC1	GATEWAY_2	IP_SUBNET_2	IP_TYPE	NET_MASK_2	USABLE_RANGE_2	WAN_IP_2
  64.60.231.79	13/HCGS/726260//PT	NULL	64.60.231.72	2	255.255.255.248	73-78	NULL
  64.60.231.79	13/HCGS/726260//PT	0	64.60.231.72	2	255.255.255.248	74-78	64.60.231.73
  NULL	13/HCGS/809421//PT	NULL	NULL	2	NULL	NULL	NULL
  NULL	NULL	NULL	NULL	2	NULL	NULL	NULL

  Please help me :( , sorry to bug you again !!

• • Reply
  

  sandeepmitta...

  Contributor

  6801 Points

  1059 Posts

  Re: Convert column values as column row

  Feb 23, 2012 08:35 AM|LINK

  SELECT View_id, Param_value, display_value INTO #temp
  FROM (
  	SELECT '299215003' AS View_id,'64.60.231.79' AS Param_value,'BROADCAST_2' AS display_value
  	UNION ALL SELECT '299215003','13/HCGS/726260//PT','CUR_CIRCUIT_LEC1'
  	UNION ALL SELECT '299215003','64.60.231.72','IP_SUBNET_2'
  	UNION ALL SELECT '299215003','2','IP_TYPE'
  	UNION ALL SELECT '299215003','255.255.255.248','NET_MASK_2'
  	UNION ALL SELECT '299215003','73-78','USABLE_RANGE_2'
  	UNION ALL SELECT '323935003','64.60.231.79','BROADCAST_2'
  	UNION ALL SELECT '323935003','13/HCGS/726260//PT','CUR_CIRCUIT_LEC1'
  	UNION ALL SELECT '323935003','0','GATEWAY_2'
  	UNION ALL SELECT '323935003','64.60.231.72','IP_SUBNET_2'
  	UNION ALL SELECT '323935003','2','IP_TYPE'
  	UNION ALL SELECT '323935003','255.255.255.248','NET_MASK_2'
  	UNION ALL SELECT '323935003','74-78','USABLE_RANGE_2'
  	UNION ALL SELECT '323935003','64.60.231.73','WAN_IP_2'
  	UNION ALL SELECT '1052351003','13/HCGS/809421//PT','CUR_CIRCUIT_LEC1'
  	UNION ALL SELECT '1052351003','2','IP_TYPE'
  	UNION ALL SELECT '1052352003','2','IP_TYPE'
  ) TAB
  
  SELECT View_id
  		, MAX(BROADCAST_2) AS BROADCAST_2
  		, MAX(CUR_CIRCUIT_LEC1) AS CUR_CIRCUIT_LEC1
  		, MAX(GATEWAY_2) AS GATEWAY_2
  		, MAX(IP_SUBNET_2) AS IP_SUBNET_2
  		, MAX(IP_TYPE) AS IP_TYPE
  		, MAX(NET_MASK_2) AS NET_MASK_2
  		, MAX(USABLE_RANGE_2) AS USABLE_RANGE_2
  		, MAX(WAN_IP_2) AS WAN_IP_2
  FROM (
  	SELECT View_id
  			, CASE WHEN display_value = 'BROADCAST_2' THEN Param_value ELSE NULL END AS BROADCAST_2
  			, CASE WHEN display_value = 'CUR_CIRCUIT_LEC1' THEN Param_value ELSE NULL END AS CUR_CIRCUIT_LEC1
  			, CASE WHEN display_value = 'GATEWAY_2' THEN Param_value ELSE NULL END AS GATEWAY_2
  			, CASE WHEN display_value = 'IP_SUBNET_2' THEN Param_value ELSE NULL END AS IP_SUBNET_2
  			, CASE WHEN display_value = 'IP_TYPE' THEN Param_value ELSE NULL END AS IP_TYPE
  			, CASE WHEN display_value = 'NET_MASK_2' THEN Param_value ELSE NULL END AS NET_MASK_2
  			, CASE WHEN display_value = 'USABLE_RANGE_2' THEN Param_value ELSE NULL END AS USABLE_RANGE_2
  			, CASE WHEN display_value = 'WAN_IP_2' THEN Param_value ELSE NULL END AS WAN_IP_2
  	FROM #temp
  ) TAB GROUP BY VIEW_ID
  
  DROP TABLE #temp

  
  Sandeep Mittal | My Blog - IT Developer Zone
  
  
  • Marked as answer by vishalvl on Feb 23, 2012 02:19 PM
• • Reply
  

  vishalvl

  Member

  62 Points

  51 Posts

  Re: Convert column values as column row

  Feb 23, 2012 02:19 PM|LINK

  Yes , it worked . Thanks a lot man !!