Solved

SQL QUERY HELP

Posted on 2014-11-26
1
122 Views
Last Modified: 2014-11-26
I have the following table.

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

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

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

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

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

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

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

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

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

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

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

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

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

Open in new window


I would like to have the following output

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

I used the below query for achieving this output:

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


i got the following output when i execute this query


Bangalore      [Book1: DWE] [Book2: ASD] [Book3: ADF]
Chennai           [Book1: AAA] [Book2: BBB] [Book3: A1A]
London          NULL
Mumbai          [Book1: SAS] [Book2: SAY] [Book3: MBM]

For the City London the activestatus for Book1 and Book2 is 'Y'
Then Why it is showing NULL ?  How do i show the Book1 and Book2 for London ?
0
Comment
Question by:Varshini S
1 Comment
 
LVL 49

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40466706
It's because you are concatenating with NULL value. Just test if is NULL or not:
SELECT City, 
   ISNULL('[Book1: ' + Book1 + '] ','') + 
   ISNULL('[Book2: ' + Book2 + '] ','') + 
   ISNULL('[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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

680 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