Sql help - 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 2008 Forums

Transact-SQL (2008)

Sql help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-06 : 10:49:55

I need a query to seperate the values into seperate columns values


ex:12/07/10 19:09:57;SAvIT_KAMK;CLOSE;UTCIC;976558

output like

Column1            Column2    column3  Column4   Column5
----------------  ----------  -------  -------  -------
12/07/10 19:09:57 SAVIT_KAMK  CLOSE     UTCIC   976558

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 11:04:46

something like


SELECT MAX(CASE WHEN ID=1 THEN Val END) AS Column1,
MAX(CASE WHEN ID=2 THEN Val END) AS Column2,
MAX(CASE WHEN ID=3 THEN Val END) AS Column3,
MAX(CASE WHEN ID=4 THEN Val END) AS Column4,
MAX(CASE WHEN ID=5 THEN Val END) AS Column5
FROM dbo.parseValues('12/07/10 19:09:57;SAvIT_KAMK;CLOSE;UTCIC;976558',';')f

ParseValues can be fund in below link

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-06 : 11:31:33

Thanks visakh

How do i use the specify the column name in the query instead directly specify value..

('12/07/10 19:09:57;SAvIT_KAMK;CLOSE;UTCIC;976558',';')

select value from table

SELECT MAX(CASE WHEN ID=1 THEN Val END) AS Column1,
MAX(CASE WHEN ID=2 THEN Val END) AS Column2,
MAX(CASE WHEN ID=3 THEN Val END) AS Column3,
MAX(CASE WHEN ID=4 THEN Val END) AS Column4,
MAX(CASE WHEN ID=5 THEN Val END) AS Column5
FROM dbo.parseValues('12/07/10 19:09:57;SAvIT_KAMK;CLOSE;UTCIC;976558',';')f

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 11:53:31

[code]
SELECT MAX(CASE WHEN ID=1 THEN Val END) AS Column1,
MAX(CASE WHEN ID=2 THEN Val END) AS Column2,
MAX(CASE WHEN ID=3 THEN Val END) AS Column3,
MAX(CASE WHEN ID=4 THEN Val END) AS Column4,
MAX(CASE WHEN ID=5 THEN Val END) AS Column5
FROM table t
CROSS APPLY dbo.parseValues(t.Value,';')f
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-06 : 12:52:24

Would this query be faster i have in a table like 1 billion records.

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-06 : 21:58:14

Thanks Visakh !

I got an error is t.stname column invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause..


SELECT t.stname,MAX(CASE WHEN ID=1 THEN Val END) AS Column1,
MAX(CASE WHEN ID=2 THEN Val END) AS Column2,
MAX(CASE WHEN ID=3 THEN Val END) AS Column3,
MAX(CASE WHEN ID=4 THEN Val END) AS Column4,
MAX(CASE WHEN ID=5 THEN Val END) AS Column5
FROM table t
CROSS APPLY dbo.parseValues(t.Value,';')f

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-06 : 22:11:24

[code]
SELECT t.stname,
MAX(CASE WHEN ID=1 THEN Val END) AS Column1,
MAX(CASE WHEN ID=2 THEN Val END) AS Column2,
MAX(CASE WHEN ID=3 THEN Val END) AS Column3,
MAX(CASE WHEN ID=4 THEN Val END) AS Column4,
MAX(CASE WHEN ID=5 THEN Val END) AS Column5
FROM table t
CROSS APPLY dbo.parseValues(t.Value,';') f
GROUP BY t.stname
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-07 : 03:40:29

Also refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/11/splitting-delimited-data-to-columns-set-based-approach.aspx

Madhivanan

Failing to plan is Planning to fail

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-12-07 : 15:39:30

It looks it has performance issue with query..
Suggest me any best solutions...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 00:37:19

why? how much time its taking? what does query plan suggests?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources