Resultset Format
in [Server Configuration]
|
From: Muhammad Bilal on 30 Jan 2010 03:54 Hi. I have a table with column. Erpno Monday Tuesday 10001 01,101,66 055,109,303 I want the upper table values in the following format. Erpno Code Weakday Date 10001 1 Monday 25/01/2010 10001 101 Monday 25/01/2010 10001 66 Monday 25/01/2010 10001 055 Tuesday 26/01/2010 10001 109 Tuesday 26/01/2010 10001 303 Tuesday 26/01/2010 Regards, Muhammad Bilal
From: Erland Sommarskog on 30 Jan 2010 10:19 Muhammad Bilal (MuhammadBilal(a)discussions.microsoft.com) writes: > Hi. > I have a table with column. > > Erpno Monday Tuesday > 10001 01,101,66 055,109,303 > > I want the upper table values in the following format. > > Erpno Code Weakday Date > 10001 1 Monday 25/01/2010 > 10001 101 Monday 25/01/2010 > 10001 66 Monday 25/01/2010 > 10001 055 Tuesday 26/01/2010 > 10001 109 Tuesday 26/01/2010 > 10001 303 Tuesday 26/01/2010 Have a look at http://www.sommarskog.se/arrays-in-sql-2005.html#tablelists for how deal with comma-separated lists in a table column. (Which is a very bad idea.) -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Muhammad Bilal on 30 Jan 2010 18:05 HI. Thankx for your reply. I am using the following function for a single column with comma values. Problem is that i cannot understand how to use it for more than one comma valued columns. As in my case I have 6 columns. CREATE FUNCTION dbo.Tf_intcommatable (@distcode INT, @svpname VARCHAR(55), @ucnbr VARCHAR(11)) RETURNS @ict TABLE(distcode INT, svpname VARCHAR(50), ucnbr INT) AS BEGIN DECLARE @ndx INT SET @ndx = 1 SET @ucnbr = Replace(@ucnbr,' ','') WHILE (Len(@ucnbr) > 0) BEGIN SET @ndx = Charindex(',',@ucnbr,1) IF @ndx = 0 BEGIN INSERT @ict VALUES(@distcode, @svpname, @ucnbr) SET @ucnbr = '' END ELSE BEGIN INSERT @ict VALUES(@distcode, @svpname, Left(@ucnbr,@ndx - 1)) SET @ucnbr = Right(@ucnbr,Len(@ucnbr) - @ndx) END END RETURN END GO SELECT * FROM dbo.Tf_intcommatable(352,'John','01,25,999') Regards, Muhammad Bilal "Erland Sommarskog" wrote: > Muhammad Bilal (MuhammadBilal(a)discussions.microsoft.com) writes: > > Hi. > > I have a table with column. > > > > Erpno Monday Tuesday > > 10001 01,101,66 055,109,303 > > > > I want the upper table values in the following format. > > > > Erpno Code Weakday Date > > 10001 1 Monday 25/01/2010 > > 10001 101 Monday 25/01/2010 > > 10001 66 Monday 25/01/2010 > > 10001 055 Tuesday 26/01/2010 > > 10001 109 Tuesday 26/01/2010 > > 10001 303 Tuesday 26/01/2010 > > Have a look at http://www.sommarskog.se/arrays-in-sql-2005.html#tablelists > for how deal with comma-separated lists in a table column. (Which is a very > bad idea.) > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > . >
From: Muhammad Bilal on 30 Jan 2010 18:11 And I am using SQL Server 2000 EE. Regards, Muhammad Bilal "Erland Sommarskog" wrote: > Muhammad Bilal (MuhammadBilal(a)discussions.microsoft.com) writes: > > Hi. > > I have a table with column. > > > > Erpno Monday Tuesday > > 10001 01,101,66 055,109,303 > > > > I want the upper table values in the following format. > > > > Erpno Code Weakday Date > > 10001 1 Monday 25/01/2010 > > 10001 101 Monday 25/01/2010 > > 10001 66 Monday 25/01/2010 > > 10001 055 Tuesday 26/01/2010 > > 10001 109 Tuesday 26/01/2010 > > 10001 303 Tuesday 26/01/2010 > > Have a look at http://www.sommarskog.se/arrays-in-sql-2005.html#tablelists > for how deal with comma-separated lists in a table column. (Which is a very > bad idea.) > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > . >
From: Erland Sommarskog on 31 Jan 2010 06:48
Muhammad Bilal (MuhammadBilal(a)discussions.microsoft.com) writes: > And I am using SQL Server 2000 EE. Please always post which version of SQL Server you are using. The relevant section for is http://www.sommarskog.se/arrays-in-sql-2000.html#unpack-tblcol You will find that the options on SQL 2000 are far more bleak. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |