Resultset Format

Muhammad Bilal (MuhammadBilalatdiscussionsdotmicrosoft.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-s...tml#tablelists
for how deal with comma-separated lists in a table column. (Which is a very
bad idea.)


--
Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

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/prodinf...ons/books.mspx

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 (MuhammadBilalatdiscussionsdotmicrosoft.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-s...tml#tablelists
> for how deal with comma-separated lists in a table column. (Which is a very
> bad idea.)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse
>
> 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/prodinf...ons/books.mspx
>
> .
>

And I am using SQL Server 2000 EE.

Regards,
Muhammad Bilal


"Erland Sommarskog" wrote:

> Muhammad Bilal (MuhammadBilalatdiscussionsdotmicrosoft.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-s...tml#tablelists
> for how deal with comma-separated lists in a table column. (Which is a very
> bad idea.)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse
>
> 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/prodinf...ons/books.mspx
>
> .
>

Muhammad Bilal (MuhammadBilalatdiscussionsdotmicrosoft.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-s...#unpack-tblcol

You will find that the options on SQL 2000 are far more bleak.

--
Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

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/prodinf...ons/books.mspx

Muhammad Bilal (MuhammadBilalatdiscussionsdotmicrosoft.com) writes:
> 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.

You have to get the values into variables since you are on SQL 2000.
You cannot pass a table column to table-UDF in SQL 2000.



--
Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

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/prodinf...ons/books.mspx