SQL-query for displaying. - 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

SQL Server 2008 Forums

Transact-SQL (2008)

SQL-query for displaying.

Author

Topic

chinlax
Starting Member

30 Posts

Posted - 2011-10-05 : 00:39:28

Hi All,
I have table say ABC
BOOkname, Pudid
Book1a P001
Book2a P001
Book3a P002

But in my task i need to display them as

Bookname
Book1a, Book2a
Book2a

I am able to display Book1a, Book2a
DECLARE @Ids VARCHAR(1000)
SELECT top(2) @Ids=ISNULL(@IDs+',','') +CAST(bookname AS VARCHAR ) ;
Print @Ids;
the above query returns only first row ,
but i want the second row to display as Book2a
Can any one help me out.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 00:44:28

how book2a repeat in second row? shouldnt it be Book3a?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 00:46:01

if its Book3a, you can use below


SELECT Pudid,
STUFF((SELECT ',' + BookName FROM ABC WHERE Pudid=t.Pudid FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT Pudid FROM ABC)t

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

chinlax
Starting Member

30 Posts

Posted - 2011-10-05 : 03:37:29

Thanks it worked out ,

But i have one more query on the similar table
This is the given table
Libid PUBID Bookname
L001 ABC Book1A
L001 ABC Book2A
L001 XYZ Book1XYZ
L001 XXX Book1X
L001 XXX Book2X
L001 DEF Book1D
L002 DEF Book2D
L002 GHI Book1G
L002 ABC Book2A

We should show the output as

Target Table
LibID ABC DEF GHI XXX XYZ
L001 Book1,Book2 NULL NULL Book1x,Book2x Book1xyz
L002 Book2 Book1D, Book2D Book1G NULL NULL

Can u help .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 04:16:49

sorry you output is confusing. can you format it correctly and show

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

chinlax
Starting Member

30 Posts

Posted - 2011-10-05 : 04:46:10

ok the space is not enough to produce it properly so i will write each coloumn separately .

LIBID
LOO1
LOO2

ABC
Book1A,Book2A
Book2A

DEF
NULL
Book1D,Book2D

GHI
NULL
Book1G

XXX
Book1x,Book2x
NULL

XYZ
Book1XYZ
NULL

These are the coloumns , this is like i need to display.
I could not copy the table structure, so only i wrote it like this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 04:51:25

[code]
SELECT Libid,
STUFF((SELECT ',' + Bookname FROM table WHERE PUBID='ABC' AND Libid=t.Libid ORDER BY Bookname FOR XML PATH('')),1,1,'') AS ABC,
STUFF((SELECT ',' + Bookname FROM table WHERE PUBID='DEF' AND Libid=t.Libid ORDER BY Bookname FOR XML PATH('')),1,1,'') AS DEF,
STUFF((SELECT ',' + Bookname FROM table WHERE PUBID='GHI' AND Libid=t.Libid ORDER BY Bookname FOR XML PATH('')),1,1,'') AS GHI,
STUFF((SELECT ',' + Bookname FROM table WHERE PUBID='XXX' AND Libid=t.Libid ORDER BY Bookname FOR XML PATH('')),1,1,'') AS XXX,
STUFF((SELECT ',' + Bookname FROM table WHERE PUBID='XYZ' AND Libid=t.Libid ORDER BY Bookname FOR XML PATH('')),1,1,'') AS XYZ
FROM (SELECT DISTINCT Libid FROM table)t
[/code]

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

chinlax
Starting Member

30 Posts

Posted - 2011-10-05 : 05:15:07

Thanks a lot.....

Its working 100% fine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 05:40:28

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources