Query - 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

General SQL Server Forums

New to SQL Server Programming

Query

Author

Topic

asm
Posting Yak Master

140 Posts

Posted - 2012-02-16 : 06:19:40

Data Store in a table -->
Code Road Width Length
LM000001 1 8.0000 0.0000
LM000001 10 8.0000 0.0000
LM000002 10 6.0000 13.0000
LM000002 11 7.0000 8.0000
LM000002 13 8.0000 0.0000

--> Output should be like
Code Road
LM000001 1(8-0), 10(8-0)
LM000002 10(6-13), 11(7-8), 13(8-0)

Please guide how to achive this by query?

Thanks

asm

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 09:55:05

sure..do the formatting in the front end

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-16 : 15:59:42

its ugly but can be done as follows

SELECT Code,
STUFF((SELECT ',' + CAST(Road as varchar(5)) + '(' + CAST(width AS varchar(5))+ ',' + CAST(length AS varchar(5)) + ')'),1,1,'')
FROM (SELECT DISTINCT Code FROM table) t

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

asm
Posting Yak Master

140 Posts

Posted - 2012-02-17 : 02:30:52

When i try this query ... error through "invalid column name Road, Width, Length.."

asm
Posting Yak Master

140 Posts

Posted - 2012-02-17 : 05:31:52

Try to explain with table and query

CREATE TABLE #test
(
ID INT,
Status VARCHAR(50)
)

INSERT INTO #test(ID,Status)
SELECT 2 ,'ACTIVE'
UNION
SELECT 2 ,'INACTIVE'
UNION
SELECT 2 ,'BOTH'
UNION
SELECT 3 ,'ACTIVE'
UNION
SELECT 3 ,'INACTIVE'

SELECT * from #test

--> Result expected
--ID Status
--2 ACTIVE,AINCTIVE,BOTH
--3 ACTIVE,AINCTIVE

SELECT DISTINCT ID ,( SELECT ',' + status as 'data()' FROM #test FOR XML PATH('')) FROM #test

-- But by this query result coming
--2 ,ACTIVE ,BOTH ,INACTIVE ,ACTIVE ,INACTIVE
--3 ,ACTIVE ,BOTH ,INACTIVE ,ACTIVE ,INACTIVE

drop table #test

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 13:16:42

quote:
Originally posted by asm

When i try this query ... error through "invalid column name Road, Width, Length.."

sorry it whould be


SELECT Code,
STUFF((SELECT ',' + CAST(Road as varchar(5)) + '(' + CAST(width AS varchar(5))+ ',' + CAST(length AS varchar(5)) + ')' FROM table WHERE Code = t.Code),1,1,'')
FROM (SELECT DISTINCT Code FROM table) t

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources