Solved

SQL QUERY HELP

Posted on 2014-11-25
9
295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 66

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 66

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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

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
 
LVL 66

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 66

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 66

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 49

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

636 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