Parsing values

[ajaeger]

I know how to do this in Access, but am having trouble in SQL...

I have a table, Table1:

ID VALUES
1 A,B,C
2 A,D

I need to convert it into the following format in Table2:
ID VALUES
1 A
1 B
1 C
2 A
2 D

How can I do this in SQL? Thanks.

Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[RyanEK]

Hi,

Are you using SQL2K5? If so then this should work...

declare @table1 Table (id int, DelimStr varchar(10))
insert into @table1(id, DelimStr) values(1,'A,B,C')
insert into @table1(id, DelimStr) values(2,'A,D')

;with cte as (
  select
    id,
    cast('<i>' + replace(DelimStr, ',', '</i><i>') + '</i>' as xml) AS DelimStr
  from @table1
)
select
  id,
  x.i.value('.', 'varchar(10)') AS DelimStr
from cte
cross apply DelimStr.nodes('//i') x(i)

Ryan

 

[hwkranger]

You can do this one of two ways,

1) use XML like listed above (and below)

ALTER PROCEDURE [dbo].[uspRateZoneStructure_AddFromXML]
 
	@XML as xml

AS

--<?xml version="1.0" encoding="utf-8"?>
--<RateZoneStructure>
--      <RateZone>1</RateZone>
--      <ContractorID>1</ContractorID>
--		<EffectiveDate>01/01/01</EffectiveDate>
--      <Rate>
--            <RateTypeID>1</RateTypeID>
--            <RateAmount>50.00</RateAmount>
--      </Rate>
--      <Rate>
--            <RateTypeID>2</RateTypeID>
--            <RateAmount>65.00</RateAmount>
--      </Rate>
--      <Rate>
--            <RateTypeID>3</RateTypeID>
--            <RateAmount>165.00</RateAmount>
--      </Rate>
--      <Rate>
--            <RateTypeID>4</RateTypeID>
--            <RateAmount>55.00</RateAmount>
--      </Rate>
--      <Rate>
--            <RateTypeID>5</RateTypeID>
--            <RateAmount>15.00</RateAmount>
--      </Rate>
--</RateZoneStructure>


	BEGIN
		SELECT
		-- NEEDS EFFECTIVE DATE
		 R.i.value('(../ContractorID)[1]', 'int')  as ContractorID,
		 R.i.value('(../RateZoneID)[1]', 'int')  as RateZoneID,
		 R.i.value('(RateTypeID)[1]', 'int')  as RateTypeID,
		 R.i.value('(RateTypeID)[1]', 'money')  as RateAmount

		FROM @XML.nodes ('//RateZoneStructure/Rate') R(i)
	END
... <SNIP>  Do your work </Snip>

2) Use a Comma delimited List

From the aspnet_usersInRoles_AddusersToRoles Procedure from the .net security block
	@ApplicationName  nvarchar(256),
	@UserNames		  nvarchar(4000),
	@RoleNames		  nvarchar(4000),
	@CurrentTimeUtc   datetime


	DECLARE @tbNames	table(Name nvarchar(256) NOT NULL PRIMARY KEY)
	DECLARE @tbRoles	table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @tbUsers	table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
	DECLARE @Num		int
	DECLARE @Pos		int
	DECLARE @NextPos	int
	DECLARE @Name		nvarchar(256)

	SET @Num = 0
	SET @Pos = 1
	WHILE(@Pos <= LEN(@RoleNames))
	BEGIN
		SELECT @NextPos = CHARINDEX(N',', @RoleNames,  @Pos)
		IF (@NextPos = 0 OR @NextPos IS NULL)
			SELECT @NextPos = LEN(@RoleNames) + 1
		SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
		SELECT @Pos = @NextPos+1

		INSERT INTO @tbNames VALUES (@Name)
		SET @Num = @Num + 1
	END

Hope this helps.