Solved

SQL QUERY HELP

Posted on 2014-11-25
9
293 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 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query output (script) from a stored procedure 4 49
How come this XML node is not read? 3 52
Database Owner 3 43
SQL query joining 6 tables in asp.net 4 26
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 …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 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