Solved

SQL QUERY HELP

Posted on 2014-11-25
9
287 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
Comment Utility
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
Comment Utility
I have added this condition. But it return NULL values for all BookDetails column
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL query 4 24
Calculating Business Hours 18 58
encyps queries mssql 15 24
Job Dependencies in SQL Server Agent: 1 11
In this article I will describe the Detach & Attach 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

8 Experts available now in Live!

Get 1:1 Help Now