Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

problem in customising a result set .plz help.. Expand / Collapse
Author
Message
Posted Monday, September 10, 2012 12:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 12, 2013 8:31 AM
Points: 17, Visits: 80
I am getting the out put of a query as

SN NodeName NodeValue
1 Address address1
1 Address address2
1 Address address3
2 city kol
3 providerID 1
3 providerID 3
3 providerID 2

actually the result set can be different .I mean the colvalue and row value will be dynamic


now i have to just customize it to like.I have to make it generalised
ProviderId Address City
1 address1 NULL
2 address2 NULL
3 address3 kol


Please help me out..Can it be done using pivot/unpivot


Post #1356573
Posted Monday, September 10, 2012 1:35 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 8:51 AM
Points: 1,475, Visits: 8,480
Your requirement isn't clear. Could you post example tables, data and expected results in a format that allows others to run it, helping others to help you.

An example of how to do this ishttp://www.sqlservercentral.com/articles/Best+Practices/61537/


BrainDonor
Linkedin
Blog Site
Post #1356582
Posted Monday, September 10, 2012 3:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 12, 2013 8:31 AM
Points: 17, Visits: 80
I am getting the out put of a query as

SN NodeName NodeValue
1 Address address1
1 Address address2
1 Address address3
2 city kol
3 providerID 1
3 providerID 3
3 providerID 2

now i have to just customize(re-arrange) it to different record set.

ProviderId Address City
1 address1 NULL
2 address2 NULL
3 address3 kol
Post #1356603
Posted Monday, September 10, 2012 3:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:50 AM
Points: 63, Visits: 293
Is theere any releationship between ProviderId, Address and City. If then use PIVOT
Post #1356610
Posted Monday, September 10, 2012 3:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 6:04 AM
Points: 1,127, Visits: 1,599
Where in the Sample Data does it show that the city is mapped to address 3??...What is the logic behind the city being mapped to Address 3 and not 1 or 2??

The Sample data is not very clear and you need to be more specific about your requirement and the Logic behind the requirement.
Please elaborate a little more on the requirement.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1356613
Posted Monday, September 10, 2012 3:55 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:53 AM
Points: 569, Visits: 902
suvo.kundu (9/10/2012)
I am getting the out put of a query as

SN NodeName NodeValue
1 Address address1
1 Address address2
1 Address address3
2 city kol
3 providerID 1
3 providerID 3
3 providerID 2

now i have to just customize(re-arrange) it to different record set.

ProviderId Address City
1 address1 NULL
2 address2 NULL
3 address3 kol



Did not get clear idea of your requirement
Please let us know what you tried and then may be somebody can help you.


--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1356623
Posted Monday, September 10, 2012 10:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 12, 2013 8:31 AM
Points: 17, Visits: 80
Actually i have a dynamic XML.I have only the starting root node of the xml.The child nodes are dynamic keeps on changing ...I have to translate to it into a tabular form
providerID Address city
1 address1 NULL
2 address2 NULL
3 address3 kol


Declare @xml xml
Set @xml =
'<XML>
<Provider>
<providerID>1</providerID>

<Address>address1</Address>

</Provider>

<Provider>
<providerID>2</providerID>

<Address>address2</Address>

</Provider>
<Provider>
<providerID>3</providerID>

<Address>address3</Address>
<city>kol</city>

</Provider>

</XML>'


SELECT
dense_rank() OVER (ORDER BY C.value('local-name(.)', 'varchar(50)')) AS 'SN',
NodeName = C.value('local-name(.)', 'varchar(50)')
, NodeValue = C.value('(.)[1]', 'varchar(50)')
FROM @xml.nodes('/XML/Provider/*') AS T(C)

The output of this is
SN NodeName NodeValue
1 Address address1
1 Address address2
1 Address address3
2 city kol
3 providerID 1
3 providerID 3
3 providerID 2




I just need to re arrange this record set to
providerID Address city
1 address1 NULL
2 address2 NULL
3 address3 kol

plz help!!
Post #1356887
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse