sql select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

sql select statement

Author

Topic

xclaim
Starting Member

8 Posts

Posted - 2013-06-21 : 10:54:51

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-21 : 11:18:58

Use unpivot. Would be something like this:

	
SELECT * FROM
tbl
UNPIVOT (Groups FOR Lists IN ([listcol1],[listcol2],[listscol3])) u

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 11:21:46

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

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

xclaim
Starting Member

8 Posts

Posted - 2013-06-21 : 20:21:10

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-22 : 02:24:41

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources