Help needed for very simple data manipulation

Question

Post reply

Help needed for very simple data manipulation

grecci1077

SSC Enthusiast

Points: 149
More actions
January 26, 2015 at 6:41 am

#294736

Hi,
I have the following table
test 1 | test 2 | test 3 | test4 | AND | NY
test 1 | test 2 | test 3 | test4 | OR | LA
test 1 | test 2 | test 3 | test4 | OR | ME
test 5 | test 6 | test 7 | test7 | AND | LA
test 8 | test 9 | test 1 | test4 | AND | NY
test 8 | test 9 | test 1 | test4 | OR | FL
And I want this
test 1 | test 2 | test 3 | test4 | AND | NY OR LA OR ME
test 5 | test 6 | test 7 | test7 | AND | LA
test 8 | test 9 | test 1 | test4 | AND | NY OR FL
Table was generated from shredding an XML where the last 2 columns is multi layered.
I tried to do a merge statement but did not work since multiple fields qualifies. Did not have much luck with a self join either. Anybody has any ideas?

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

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

David Burrows SSC Guru Points: 65138 More actions · Answer 1

I think this a case for using 'FOR XML' to concatenate the last two columns,

however we will need the table DDL and insert statements to create the data in the table

plus more exact data for the last two columns, i.e. do AND / OR have leading/trailing spaces?

and how do you determine the sequence of the last two columns?

Far away is close at hand in the images of elsewhere.
Anon.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

Sample DDL is always helpful; I put some together for your this time. I think you are looking for something like this:

-- (1) Sample DDL and Sample data

DECLARE @yourtable TABLE

(

c1 varchar(20) not null,

c2 varchar(20) not null,

c3 varchar(20) not null,

c4 varchar(20) not null,

c5 varchar(20) not null,

c6 varchar(20) not null

);

INSERT INTO @yourtable VALUES

('test 1','test 2','test 3','test4','AND','NY'),

('test 1','test 2','test 3','test4','OR','LA'),

('test 1','test 2','test 3','test4','OR','ME'),

('test 5','test 6','test 7','test7','AND','LA'),

('test 8','test 9','test 1','test4','AND','NY'),

('test 8','test 9','test 1','test4','OR','FL');

-- (2) Solution

WITH

x AS

(

SELECTyt1.c1,

yt1.c2,

yt1.c3,

yt1.c4,

xx = (SELECT c5+' '+c6+' ' FROM @yourtable yt2 WHERE yt1.c1 = yt2.c1 FOR XML PATH(''))

FROM @yourtable yt1

)

SELECT c1, c2, c3, c4, xx

FROM x

GROUP BY c1, c2, c3, c4, xx;

Edit: Code Formatting was off...

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

Option 2

I was making the assumption that "AND"/"OR" values were not in their own column. If I was wrong, here's another option:

WITH

x AS

(

SELECTyt1.c1,

yt1.c2,

yt1.c3,

yt1.c4,

xx = (SELECT c5+' '+c6+' ' FROM @yourtable yt2 WHERE yt1.c1 = yt2.c1 FOR XML PATH(''))

FROM @yourtable yt1

)

SELECT c1, c2, c3, c4, cx1 = substring(xx, 1, charindex(' ',xx)), cx2 = substring(xx, charindex(' ',xx)+1, len(xx))

FROM x

GROUP BY c1, c2, c3, c4, xx;

Lastly,

Table was generated from shredding an XML where the last 2 columns is multi layered.

I don't know who is doing the XML shredding but why not just modify the XML code used to generate the table to get you the result that you are looking for. That seems to make more sense. Just a thought.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

grecci1077 SSC Enthusiast Points: 149 More actions · Answer 4

Thanks guys,

Thanks for taking the time to review my request. Sorry for not displaying more descriptive data. Below is the modified data that i used to produce the table (select into). This is essentially an xml file that I extracted from drive mapping in the Group Policy Preference section. As you can see, There is some uncertainty found within the <Filters> </Filters> section which makes my task more difficult.

----------------------------------------------------------------------------------------------------------

declare @Doc XML

declare @order int

SET @Doc = cast('

</Filters>

</Drive>

</Filters>

</Drive>

</FilterCollection>

</FilterCollection>

</Filters>

</Drive>

</FilterCollection>

</FilterCollection>

</Filters>

</Drive>

</Filters>

</Drive>

</Drives>' as XML)

select y.[desc].value('@letter','varchar (230)') as letter

,x.[desc].value('@desc[1]','varchar (230)') as [desc]

,y.[desc].value('@path','varchar (230)') as [path]

,y.[desc].value('@label','varchar (230)') as Label

,x.[desc].value('@changed','varchar (230)') as [Last Changed]

,z.[desc].value('@bool','varchar(10)') as condition

,z.[desc].value('@name','varchar(1000)') as Username

from @doc.nodes('/Drives/Drive') x ([desc])

outer

apply x.[desc].nodes('Properties') y ([desc])

outer

apply x.[desc].nodes('Filters/FilterUser') z ([desc])

------------------------------------------------------------------------------------------------------------------------------

Dwain Camps SSC Guru Points: 86908 More actions · Answer 5

Not sure but maybe something like this?

WITH ParsedXML AS

(

select y.[desc].value('@letter','varchar (230)') as letter

,x.[desc].value('@desc[1]','varchar (230)') as [desc]

,y.[desc].value('@path','varchar (230)') as [path]

,y.[desc].value('@label','varchar (230)') as Label

,x.[desc].value('@changed','varchar (230)') as [Last Changed]

,z.[desc].value('@bool','varchar(10)') as condition

,z.[desc].value('@name','varchar(1000)') as Username

from @doc.nodes('/Drives/Drive') x ([desc])

outer

apply x.[desc].nodes('Properties') y ([desc])

outer

apply x.[desc].nodes('Filters/FilterUser') z ([desc])

),

AddRN AS

(

SELECT *, rn=ROW_NUMBER() OVER (PARTITION BY [desc] ORDER BY condition)

FROM ParsedXML

)

SELECT letter, [desc], [path], [Label]

,Username = STUFF(

(

SELECT ' OR ' + Username

FROM AddRn b

WHERE a.[desc] = b.[desc]

ORDER BY rn

FOR XML PATH('')

), 1, 4, '')

FROM AddRn a

GROUP BY letter, [desc], [path], [Label]

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

grecci1077 SSC Enthusiast Points: 149 More actions · Answer 6

This is very closed to what I was looking for. You definitely took care of the multiple usernames associated to the Filteruser tag. But ideally, I would like keep the final set to remain in the same order.

"SELECT ' OR ' + Username" - The "OR" can also be a "AND" in other cases.

I can always edit the XML to replace Filtergroup with FilterUser, that would take care of the multiple filtergroup membership.

As far as the other (filter) variations, they come far and few in between.