Given a Promotion
table with rows and data such as:
PromotionId Codes
1 a,b
2 c
3 d,e,f
How could I write a query to return it like this:
PromotionId Codes
1 a
1 b
2 c
3 d
3 e
3 f
Basically I want to string split the Code over multiple rows.
You can use a Split
table-valued-function, for example:
CREATE FUNCTION [dbo].[Split]
(
@ItemList NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(MAX)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
Then you can use CROSS APPLY
to join with it:
SELECT PromotionId, CodeList.Item AS Codes
FROM Promotion p
CROSS APPLY dbo.Split(Codes, ',') CodeList
Edit: Here's a fiddle with your data: http://sqlfiddle.com/#!3/85f8b/1/0
Result:
PROMOTIONID CODES
1 a
1 b
2 c
3 d
3 e
3 f
What about using SQL with XML like the following:
declare @X xml
SET @X= (SELECT CONVERT(xml,'<s id="'+cast(PromotionId as varchar)+'">' + REPLACE(Codes,',','</s><s id="'+cast(PromotionId as varchar)+'">') + '</s>') FROM t1
FOR XML RAW)
SELECT c.value('(@id)[1]','int') as PromotionId, c.value('.','varchar(50)') as Code
FROM @X.nodes('/row/s') T(c)
It will be more efficient and faster
SQLFiddle Demo
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With