Solved

SQL QUERY HELP

Posted on 2014-11-25
9
290 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

821 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