Solved

SQL QUERY HELP

Posted on 2014-11-25
9
289 Views
Last Modified: 2014-11-26
I have the following table.

CREATE TABLE Table1
	([City] varchar(9), [Title] varchar(6), [Seller] varchar(25))

	
INSERT INTO Table1
	([City], [Title], [Seller])
VALUES 	('Chennai', 'Book1', 'AAA')

INSERT INTO Table1([City], [Title], [Seller])
VALUES 	('Chennai', 'Book2', 'BBB')

INSERT INTO Table1
	([City], [Title], [Seller])
VALUES ('Chennai', 'Book3', 'A1A')

INSERT INTO Table1
	([City], [Title], [Seller])
VALUES ('Mumbai', 'Book1', 'SAS')

INSERT INTO Table1
	([City], [Title], [Seller])
VALUES ('Mumbai', 'Book2', 'SAY')

INSERT INTO Table1
	([City], [Title], [Seller])
VALUES ('Mumbai', 'Book3', 'MBM')

INSERT INTO Table1
	([City], [Title], [Seller])
VALUES ('Bangalore', 'Book3','ADF')

INSERT INTO Table1
	([City], [Title], [Seller])
VALUES ('Bangalore', 'Book2', 'ASD')

INSERT INTO Table1
	([City], [Title], [Seller])
VALUES ('Bangalore', 'Book1', 'DWE')

INSERT INTO Table1
	([City], [Title], [Seller])
VALUES ('London', 'Book2', 'LLA')

INSERT INTO Table1
	([City], [Title], [Seller])
VALUES ('London', 'Book1', 'SLA')

INSERT INTO Table1
	([City], [Title], [Seller])
VALUES ('London', 'Book3', 'SSS')
        

Open in new window


I need  the following output. How to achieve this using SQL query ?

Chennai     [Book1: AAA]  [Book2:BBB]  [Book3: A1A]
Mumbai     [Book1: SAS]  [Book2:SAY]  [Book3: MBM]
Bangalore  [Book1: DWE]  [Book2:ASD]  [Book3: ADF]
London      [Book1: SLA]  [Book2:LLA]  [Book3: SSS]
0
Comment
Question by:Varshini S
  • 5
  • 3
9 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40465393
Give this a whirl.  Looks cleaner, and uses PIVOT
SELECT City, [Book1] as Book1, [Book2] as Book2, [Book3] as Book3
FROM (SELECT City, Title, Seller FROM Table1) t1
	PIVOT ( MIN(Seller) FOR Title IN ([Book1], [Book2], [Book3])) as pvt
ORDER BY City

Open in new window

0
 

Author Comment

by:Varshini S
ID: 40465427
Jim Horn:  The column value needs to be in one column.

     City                               Book Details
Chennai       [Book1: AAA]  [Book2:BBB]  [Book3: A1A]
 Mumbai     [Book1: SAS]  [Book2:SAY]  [Book3: MBM]
 Bangalore  [Book1: DWE]  [Book2:ASD]  [Book3: ADF]
 London       [Book1: SLA]  [Book2:LLA]  [Book3: SSS]
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40465444
Prolly can't pull that off in one statement, but you could always throw the above into a subquery, and do the 1-2-3 concatenation in the main query.  Changed, and added the cosmetic formatting
SELECT City, 
   '[Book1: ' + Book1 + '] ' + 
   '[Book2: ' + Book2 + '] ' + 
   '[Book3: ' + Book3 + '] ' AS BookDetails
FROM (
   SELECT City, 
      [Book1] as Book1,
      [Book2] as Book2,
      [Book3] as Book3
   FROM (SELECT City, Title, Seller FROM Table1) t1
      PIVOT (MIN(Seller) FOR Title IN ([Book1], [Book2], [Book3])) as pvt
   ) a
ORDER BY City

Open in new window

0
 

Author Comment

by:Varshini S
ID: 40465576
Jim Horn:  Thank You. In this table I have one more column called ActiveStatus.  So in this query I need a add a where condition to check only ActiveStatus='Y'.  Where should I add this condition ?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40465589
Without looking at a table schema, I'd put it in row 10.
If this doesn't work let me know..
SELECT City, 
   '[Book1: ' + Book1 + '] ' + 
   '[Book2: ' + Book2 + '] ' + 
   '[Book3: ' + Book3 + '] ' AS BookDetails
FROM (
   SELECT City, 
      [Book1] as Book1,
      [Book2] as Book2,
      [Book3] as Book3
   FROM (SELECT City, Title, Seller FROM Table1 WHERE ActiveStatus='Y') t1
      PIVOT (MIN(Seller) FOR Title IN ([Book1], [Book2], [Book3])) as pvt
   ) a
ORDER BY City

Open in new window

0
 

Author Comment

by:Varshini S
ID: 40465612
I have added this condition. But it return NULL values for all BookDetails column
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40465621
I think I've answered your base question, so let's close this one out and discuss new requirements as a new question.

One of the things you're going to have to do is figure out what to do with NULLs, i.e. what to do if there is no intersect between Book and Seller.   You'll also want to think about how to handle multiple values as well.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40465688
Just dealing with NULLs for now
SELECT City, 
   CASE WHEN Book1 IS NULL THEN '' ELSE '[Book1: ' + Book1 + '] ' END + 
   CASE WHEN Book2 IS NULL THEN '' ELSE '[Book2: ' + Book2 + '] ' END + 
   CASE WHEN Book3 IS NULL THEN '' ELSE '[Book3: ' + Book3 + '] ' END AS BookDetails
FROM (
   SELECT City, 
      [Book1] as Book1,
      [Book2] as Book2,
      [Book3] as Book3
   FROM (SELECT City, Title, Seller FROM Table1) t1
      PIVOT (MIN(Seller) FOR Title IN ([Book1], [Book2], [Book3])) as pvt
   ) a
ORDER BY City

Open in new window


Anything beyond this, please close this question out and ask another.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40465887
You can also use "FOR XML PATH" for this especially if there is a variable number of books per city
SELECT
  t1.city
, ca.details
FROM table1 t1
    CROSS APPLY (
        SELECT
        STUFF((
              SELECT
                    ' [' + table1.Title + ':' + table1.seller + ']'
              FROM table1 
              WHERE table1.city = t1.city
              ORDER BY table1.title, seller
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca (details)
group by
  t1.city
, ca.details
;

Open in new window

Results:
|      CITY |                             DETAILS |
|-----------|-------------------------------------|
| Bangalore | [Book1:DWE] [Book2:ASD] [Book3:ADF] |
|   Chennai | [Book1:AAA] [Book2:BBB] [Book3:A1A] |
|    London | [Book1:SLA] [Book2:LLA] [Book3:SSS] |
|    Mumbai | [Book1:SAS] [Book2:SAY] [Book3:MBM] |

Open in new window


see: http://sqlfiddle.com/#!3/4d60b/3
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now