SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 sql select statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xclaim
Starting Member

8 Posts

Posted - 06/21/2013 :  10:54:51  Show Profile  Reply with Quote
here is my sample data
group1 list1;list2;list3;list10
group2 list4;list5;list1

this is the output i need
group1 list1
group1 list2
group1 list3
group1 list10
group2 list4
group2 list5
group2 list1

please help

James K
Flowing Fount of Yak Knowledge

3638 Posts

Posted - 06/21/2013 :  11:18:58  Show Profile  Reply with Quote
Use unpivot. Would be something like this:
	
SELECT * FROM
tbl
UNPIVOT (Groups FOR Lists IN ([listcol1],[listcol2],[listscol3])) u
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  11:21:46  Show Profile  Reply with Quote

SELECT t.group,f.Val
FROM table t
CROSS APPLY dbo.ParseValues(t.listfield,';')f


ParseValues can be found here

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

xclaim
Starting Member

8 Posts

Posted - 06/21/2013 :  20:21:10  Show Profile  Reply with Quote
great this works : I modified the function though since i rant out of varchar

thank you thank you thank you

here is the modified version : It works great

"/****** Object: UserDefinedFunction [dbo].[ParseValues] Script Date: 6/21/2013 8:18:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ParseValues]
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (ID int identity(1,1), Val varchar(8000))
AS
BEGIN
DECLARE @Value varchar(8000)
WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END, @String=CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0 THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END
"


quote:
Originally posted by visakh16


SELECT t.group,f.Val
FROM table t
CROSS APPLY dbo.ParseValues(t.listfield,';')f


ParseValues can be found here

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/22/2013 :  02:24:41  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000