March 4, 2014 at 3:22 pm
Sample Data
MemberID Codes
00000123 012|222|123|333
00000233 012|222|332
00000244 012|211
00000332 012
I am trying to get it so as following:
MemberID Code1, Code2, Code3, Code4
I tried using the XML method but in working with an example, I actually got stuck on one of the declare fields and cant seem to work around it. Any suggestion in doing it another way would be appreciated.
Failed Attempt:
DECLARE @x xml;
DECLARE @line VARCHAR(MAX);
SET @x = Cast(
'<field>'
+ replace(@line, '|', '</field><field>')
+ '</field>' AS XML);
SELECT [Member ID],
@x.query('field[1]').value('.','VarChar(10)') CODE1
,@x.query('field[2]').value('.','VarChar(10)') CODE2
,@x.query('field[3]').value('.','VarChar(10)') CODE3
,@x.query('field[4]').value('.','VarChar(10)') CODE4
,@x.query('field[5]').value('.','VarChar(10)') CODE5
,@x.query('field[6]').value('.','VarChar(10)') CODE6
,@x.query('field[7]').value('.','VarChar(10)') CODE7
,@x.query('field[8]').value('.','VarChar(10)') CODE8
,@x.query('field[9]').value('.','VarChar(10)') CODE9
,@x.query('field[10]').value('.','VarChar(10)') CODE10
,@x.query('field[11]').value('.','VarChar(10)') CODE1
,@x.query('field[12]').value('.','VarChar(10)') CODEl2
,@x.query('field[13]').value('.','VarChar(10)') CODEl3
,@x.query('field[14]').value('.','VarChar(10)') CODEl4
,@x.query('field[15]').value('.','VarChar(10)') CODEl5
,@x.query('field[16]').value('.','VarChar(10)') CODEl6
,@x.query('field[17]').value('.','VarChar(10)') CODEl7
,@x.query('field[18]').value('.','VarChar(10)') CODEl8
,@x.query('field[19]').value('.','VarChar(10)') CODEl9
,@x.query('field[20]').value('.','VarChar(10)') CODE20
,@x.query('field[21]').value('.','VarChar(10)') CODE21
,@x.query('field[22]').value('.','VarChar(10)') CODE22
,@x.query('field[23]').value('.','VarChar(10)') CODE23
,@x.query('field[24]').value('.','VarChar(10)') CODE24
,@x.query('field[25]').value('.','VarChar(10)') CODE25
FROM #Temp88
March 4, 2014 at 3:47 pm
I believe this is what you are looking for, check out this article by Jeff Moden - http://www.sqlservercentral.com/articles/T-SQL/63003/
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 5, 2014 at 1:11 pm
The problem is i am trying to keep this adaptive to SQL 2008 and eventually to SQL 2014. That is why i am trying to stay on the XML path. But that article gave me a few ideas just not the final product i was expecting.
March 5, 2014 at 1:51 pm
I've not tried everything Jeff covers in that article in 2008, but I can tell you with certainty that the Tally table works spectacularly in 2005 and 2008. Because it's really just a way of using a table of numbers, it works well everywhere I've encountered.
I'm very interested in how the XML approach compares to the split approach in terms of performance. If you get them both working, please post any performance benchmarking you do.
March 5, 2014 at 5:21 pm
Your code nearly worked, here is a tweaked version that does.
declare @t table ([MemberID] char(8), Codes varchar(4000));
insert @t values
('00000123','012|222|123|333'),
('00000233','012|222|332'),
('00000244','012|211'),
('00000332','012')
SELECT [MemberID]
,x.query('field[1]/text()').value('.','VarChar(10)') CODE1
,x.query('field[2]/text()').value('.','VarChar(10)') CODE2
,x.query('field[3]/text()').value('.','VarChar(10)') CODE3
,x.query('field[4]/text()').value('.','VarChar(10)') CODE4
,x.query('field[5]/text()').value('.','VarChar(10)') CODE5
,x.query('field[6]/text()').value('.','VarChar(10)') CODE6
,x.query('field[7]/text()').value('.','VarChar(10)') CODE7
,x.query('field[8]/text()').value('.','VarChar(10)') CODE8
,x.query('field[9]/text()').value('.','VarChar(10)') CODE9
,x.query('field[10]/text()').value('.','VarChar(10)') CODE10
,x.query('field[11]/text()').value('.','VarChar(10)') CODE11
,x.query('field[12]/text()').value('.','VarChar(10)') CODE12
,x.query('field[13]/text()').value('.','VarChar(10)') CODE13
,x.query('field[14]/text()').value('.','VarChar(10)') CODE14
,x.query('field[15]/text()').value('.','VarChar(10)') CODE15
,x.query('field[16]/text()').value('.','VarChar(10)') CODE16
,x.query('field[17]/text()').value('.','VarChar(10)') CODE17
,x.query('field[18]/text()').value('.','VarChar(10)') CODE18
,x.query('field[19]/text()').value('.','VarChar(10)') CODE19
,x.query('field[20]/text()').value('.','VarChar(10)') CODE20
,x.query('field[21]/text()').value('.','VarChar(10)') CODE21
,x.query('field[22]/text()').value('.','VarChar(10)') CODE22
,x.query('field[23]/text()').value('.','VarChar(10)') CODE23
,x.query('field[24]/text()').value('.','VarChar(10)') CODE24
,x.query('field[25]/text()').value('.','VarChar(10)') CODE25
FROM @t as t
cross apply (
select Cast('<field>'
+ replace(t.[Codes], '|', '</field><field>')
+ '</field>' AS XML)
) as a(x)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);March 5, 2014 at 5:23 pm
problem is the data from a table, is there a way of refrofitting a table query into the value section ?
March 5, 2014 at 5:30 pm
That query works from a TABLE variable - @t, just use your table in place of @t.
To make things easier and to get better answers, try to provide consumable test data in your questions.
Check out the link "How to post data/code on a forum" in my signature to find out how to do this.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply