February 22, 2012 at 8:31 pm
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
February 22, 2012 at 10:14 pm
Try using PIVOT... That would help you for sure.
February 23, 2012 at 1:34 am
If you provide some CREATE Table Scripts and populate it with some sample data we might be able to help you a little further - might even be able to provide you with a script too 🙂
February 23, 2012 at 1:38 am
Just to add more details
i have my output query as
View_id Param_value display_value
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 ... My sql server is 2000
The procedure is
--EXEC [dbo].[usp_OM_CUST_NOTIFICATIONS_FXDEV_reconfig] 405767003
ALTER PROCEDURE [dbo].[usp_OM_CUST_NOTIFICATIONS_FXDEV_reconfig] (
@ORDER_ID INT = 0 )
AS
BEGIN
SET NOCOUNT ON;
SELECT a.view_id,a.param_value , p.display_value INTO #IP_DETAIL
FROM service_ext_data a , param_values p
WHERE view_id IN (SELECT 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 = CONVERT(VARCHAR(10),@ORDER_ID)
AND wp_process_id = 'Rcnfg-Circuit'
)
)
)
AND p.param_id in(10268,10298,10354,10170,10208,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)
, [CUR_CIRCUIT_LEC1] 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
February 23, 2012 at 2:28 am
I'd use the CrossTab method as described in the related link in my signature.
To apply the concep to your scenario, it would look something like
SELECT
MAX(CASE WHEN display_value='BROADCAST_2'THEN Param_value ELSE NULL END ) AS BROADCAST_2,
MAX(CASE WHEN display_value='CUR_CIRCUIT_LEC1'THEN Param_value ELSE NULL END ) AS CUR_CIRCUIT_LEC1
FROM YourTableOrQuery
GROUP BY View_id
As a side note: SQL2000 is far behind something called a "current version"... Did you already consider to upgrade it to at least 2008R2 (or even wait for SQL2012)?
February 23, 2012 at 8:23 am
Yes man it worked Thanks a lot, let me know how to mark it as a answer?
February 23, 2012 at 8:37 am
visaforuk (2/23/2012)
Yes man it worked Thanks a lot, let me know how to mark it as a answer?
You can't mark it as answer here at SSC. The concept over here is based on an "open discussion". There might be a where you mark a post as "answer" but there's a much better solution available, just posted later.
We usually appreciate a feedback like you just did.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply