split function - 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 2000 Forums

SQL Server Development (2000)

split function

Author

Topic

folumike
Starting Member

24 Posts

Posted - 2012-05-21 : 18:35:07

How do i split the table below
USING MSSQL 2000

Before

Date | Warehouse | BegBalance
Apr 2 2012 W1,W2,W3 | 20/40/10
Apr 4 2012 | W1,W2,W3 | 10/30/20

AFTER

Date | Warehouse | BegBalance
Apr 2 2012 | W1 | 20
Apr 2 2012 | W2 | 40
Apr 2 2012 | W3 | 10
Apr 4 2012 | W1 | 10
Apr 4 2012 | W2 | 30
Apr 4 2012 | W3 | 20

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 18:44:31

will it be always 3 Warehouses max?

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

folumike
Starting Member

24 Posts

Posted - 2012-05-21 : 18:53:43

No, it could be more than 3 warehouses

quote:
Originally posted by visakh16

will it be always 3 Warehouses max?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 19:09:58

see

http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

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

folumike
Starting Member

24 Posts

Posted - 2012-05-21 : 19:22:14

Thanks so much.
But how about if the warehouses are 3 (max)

quote:
Originally posted by visakh16

see

http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 19:27:54

hmm... if its only up to maximum of 4 rows you can use


SELECT Date,PARSENAME(REPLACE(Warehouse,',','.'),1) AS WareHouse,PARSENAME(REPLACE(BegBalance,'/','.'),1) AS BegBalance
FROM table

UNION ALL

SELECT Date,PARSENAME(REPLACE(Warehouse,',','.'),2),PARSENAME(REPLACE(BegBalance,'/','.'),2)
FROM table
WHERE PARSENAME(REPLACE(Warehouse,',','.'),2) IS NOT NULL
OR PARSENAME(REPLACE(BegBalance,'/','.'),2) IS NOT NULL

UNION ALL

SELECT Date,PARSENAME(REPLACE(Warehouse,',','.'),3),PARSENAME(REPLACE(BegBalance,'/','.'),3)
FROM table
WHERE PARSENAME(REPLACE(Warehouse,',','.'),3) IS NOT NULL
OR PARSENAME(REPLACE(BegBalance,'/','.'),3) IS NOT NULL

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources