problem in customising a result set .plz help..

Question

problem in customising a result set .plz help..

suvo.kundu

SSC Eights!

Points: 953
More actions
September 10, 2012 at 12:49 am

#263949

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

BrainDonor SSCoach Points: 19254 More actions · Answer 1

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/

Steve Hall
Linkedin
Blog Site

suvo.kundu SSC Eights! Points: 953 More actions · Answer 2

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

Sony Francis @EY SSCommitted Points: 1539 More actions · Answer 3

Is theere any releationship between ProviderId, Address and City. If then use PIVOT

vinu512 SSCoach Points: 15729 More actions · Answer 4

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[/url] 😉

rhythmk SSCertifiable Points: 7271 More actions · Answer 5

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

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

suvo.kundu SSC Eights! Points: 953 More actions · Answer 6

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>

<Address>address1</Address>

</Provider>

<Address>address2</Address>

</Provider>

<Address>address3</Address>

</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

1Addressaddress1

1Addressaddress2

1Addressaddress3

2city kol

3providerID1

3providerID3

3providerID2

I just need to re arrange this record set to

providerID Address city

1 address1 NULL

2 address2 NULL

3 address3 kol

plz help!!