sql server query to combine all rows into one column

Posted by Lakn2 under Sql Server on 6/22/2011 | Points: 10 | Views : 4213 | Status : [Member] | Replies : 8
hi

i have one table like

months       emloyees  departments   salesprice  products
january asif sales 100 a
february 200 b
march 300 c
april 400 d
.....
.....
january vijay sales&marketing 30 x
february 70 y
......
now i want output like

january,february,march,... asif sales 100,200,300.... a,b,c...
january,february,march.... vijay s&m 30,70.... x,y...


Thanks&Regards
LakshmiNarayana Nalluri.



Responses

Posted by: SheoNarayan on: 6/22/2011 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
Do like this

SELECT Cast(AutoId as varchar) + ',' + FirstName + ',' + LastName FROM PersonalDetail 


In this case the Column names are AutoId, FirstName and LastName and the table name is PersonalDetail.

Remember to use Cast function in case your column data type is not string.

Hope this helps.

Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 6/23/2011 [Member] Starter | Points: 25

Up
0
Down
hi
your query is not working would you please try with my columns
in my table datatypes are months,employees,departments,products are varchar datatype salesprice is money i had tried woth stuff function but it combine all rows into one column .but i don't want that .i want asif records separately,vijay records ......


Thanks&Regards
LakshmiNarayana Nalluri.

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jpchoudhari on: 6/23/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Lakn2,

You can use SQL PIVOT to convert your rows into columns.

http://blog.sqlauthority.com/2008/05/22/sql-server-pivot-table-example/
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

Thanks and Regards,
Jaydeep Choudhari

Kind Regards,
Jay

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 6/23/2011 [Member] Starter | Points: 25

Up
0
Down
hi using pivot table we can show rows as column and column as rows but my requirement is not like that.

Thanks&Regards
LakshmiNarayana Nalluri.

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakn2 on: 6/24/2011 [Member] Starter | Points: 25

Up
0
Down
hi SheoNarayan

thank you for your reply i got answer using COALESCE function with some modifications

Thanks&Regards
LakshmiNarayana Nalluri.

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/20/2012 [Member] Starter | Points: 25

Up
0
Down
SELECG FIRSTNAME+ ','+ SECONDNAME + ', ' + LASTNAME + ', ' + ADDRESS AS NAMES FROM EMPLOYEE

REGARDS


sriram

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Somasundaram on: 5/21/2012 [Member] Starter | Points: 25

Up
0
Down
First Use this Function to Concatenate string according to your table name:

    ALTER FUNCTION [dbo].[fnConcatenate]  (@id INT)

RETURNS VARCHAR(8000)
AS
BEGIN

DECLARE @v VARCHAR(8000)
SELECT @v =''

SELECT @v =@v + columnname+ ','
FROM tablename
where ID= @id
SELECT @v = LEFT(@v,(LEN(@v)-1))
return @v
END


then use Group by to get single record of each employee..
Hope this will work, awaiting for ur reply...

Regards,
Somasundaram

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Somasundaram on: 5/21/2012 [Member] Starter | Points: 25

Up
0
Down
In "Select" query,
such as:

select Id,dbo.fnConcatenate(id) from tablename

group by id,dbo.fnConcatenate(id)



Regards,
Somasundaram

Lakn2, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response