Query for displaying a particular set of data

[Raman Pahwa]

Hi,

I have a table with fields number,category ,date and if i want to choose numbers of a particular category and date.
then the query will b
select number from <table name> where category=" " and date =" "
and it will display a list of numbers.

And wat if i want those numbers horizontally.Su ppose i hav three numbers
123
234
678
and i want it to be like this
123 234 678
but it should be done automatically. is there any query for that.

[rsrinivasan]

Try this, and reply me

DECLARE @numberList varchar(255)
Select @numberList = IsNull(number + ',' + @numberList, NULLIF( number, @numberList)) from <tablename>   where <condition>
Select @numberList as numberList

[ck9663]

If this is a big data and you're using sql2005, use UN/PIVOT...

-- CK

[Raman Pahwa]

Thanks a lot.It works.

And wat if I want to select number and category where date is given like

number category date
123 1 8/12/2008
234 1 8/12/2008
123 2 8/12/2008
789 2 8/12/2008
5678 1 8/12/2008

then i should get:

numberlist category
123,234,5678 1
123,789 2

can u plz help me in this.

[Raman Pahwa]

Try this, and reply me

DECLARE @numberList varchar(255)
Select @numberList = IsNull(number + ',' + @numberList, NULLIF( number, @numberList)) from <tablename>   where <condition>
Select @numberList as numberList

Thanks a lot.Ur query helps me a lot

i want to store the data from variable to a new table.can it b possible.plz reply

[Raman Pahwa]

Try this, and reply me

DECLARE @numberList varchar(255)
Select @numberList = IsNull(number + ',' + @numberList, NULLIF( number, @numberList)) from <tablename>   where <condition>
Select @numberList as numberList

Hi,

I want the variable data in a new table.cud u plz help me.

[ck9663]

You can parse the variable.

But I'd still say you use PIVOT/UNIPVOT, if you're running sql 2005.

-- CK

[Raman Pahwa]

You can parse the variable.

But I'd still say you use PIVOT/UNIPVOT, if you're running sql 2005.

-- CK

would u plz help me in that?

[amitpatel66]

PIVOT for MSSQL 2005

[Raman Pahwa]

PIVOT for MSSQL 2005

i dont know how to use PIVOT. I have only one table.i have to concatenate the multiple records into single record.
Can u send me a query related to my prob.