Help with a query please....

I have two tables, data_branchAddOns:
CREATE TABLE [dbo].[data_branchAddOns] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[branchId] [int] NOT NULL ,
[name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[displayText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[cost] [decimal](5, 2) NOT NULL ,
[ipt] [decimal](5, 2) NOT NULL ,
[adminCharge] [decimal](5, 2) NOT NULL ,
[deleted] [bit] NULL
)
data_branchDetails:
CREATE TABLE [dbo].[data_branchDetails] (
[id] [int] IDENTITY (1000, 1) NOT NULL ,
[name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[address3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[address4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[postcode] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[telephone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[fax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[website] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[b2b] [bit] NOT NULL ,
[discountOffPlan] [decimal](5, 2) NOT NULL ,
[discount1to4] [decimal](5, 2) NOT NULL ,
[discount5to19] [decimal](5, 2) NOT NULL ,
[discount20plus] [decimal](5, 2) NOT NULL ,
[loading] [decimal](5, 2) NOT NULL ,
[dateAdded] [datetime] NOT NULL ,
[deleted] [bit] NOT NULL
)
One branch in branchDetails can have many branchAddOns. The key is ID
in branchDetails, and the foreign key in branchAddOns is branchId.
I have a problem in that I need to return a dataset in the form:
BranchName AddOnName1 AddOnCost1 AddOnName2 AddOnCost2 AddOnName3
AddOnCost3 ... to 5
So basically I'm turning the table on its side and mapping the related
records to be fields. Any idea how I can write a query to do this?
There are only ever 5 'Add Ons' related to each 'Branch' so I am okay
to map the field names in my procedure as I dont think it would be
possible to have a dynamic number of fields returned?
Thanks in advanceHi Chris,
you can write a correlated subquery and map different addon to different
column of your result set if there are a specific number of addons.
If there are differen number of addons for different branches, then the best
way is to fix the schema ouput with the max number of addon column and do th
e
same thing. You might wanna just load them with NULLS or something as per
your requirement.
hope this helps
Abhishek
"Chris Ashley" wrote:

> I have two tables, data_branchAddOns:
> CREATE TABLE [dbo].[data_branchAddOns] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [branchId] [int] NOT NULL ,
> [name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [displayText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [cost] [decimal](5, 2) NOT NULL ,
> [ipt] [decimal](5, 2) NOT NULL ,
> [adminCharge] [decimal](5, 2) NOT NULL ,
> [deleted] [bit] NULL
> )
> data_branchDetails:
> CREATE TABLE [dbo].[data_branchDetails] (
> [id] [int] IDENTITY (1000, 1) NOT NULL ,
> [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [postcode] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [telephone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [fax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [website] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [b2b] [bit] NOT NULL ,
> [discountOffPlan] [decimal](5, 2) NOT NULL ,
> [discount1to4] [decimal](5, 2) NOT NULL ,
> [discount5to19] [decimal](5, 2) NOT NULL ,
> [discount20plus] [decimal](5, 2) NOT NULL ,
> [loading] [decimal](5, 2) NOT NULL ,
> [dateAdded] [datetime] NOT NULL ,
> [deleted] [bit] NOT NULL
> )
> One branch in branchDetails can have many branchAddOns. The key is ID
> in branchDetails, and the foreign key in branchAddOns is branchId.
>
> I have a problem in that I need to return a dataset in the form:
> BranchName AddOnName1 AddOnCost1 AddOnName2 AddOnCost2 AddOnName3
> AddOnCost3 ... to 5
> So basically I'm turning the table on its side and mapping the related
> records to be fields. Any idea how I can write a query to do this?
> There are only ever 5 'Add Ons' related to each 'Branch' so I am okay
> to map the field names in my procedure as I dont think it would be
> possible to have a dynamic number of fields returned?
> Thanks in advance
>|||Hi Chris
You may want to try something like:
SELECT B.name AS BranchName,
( SELECT A.name FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELECT
COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id )
= 1 ) AS AddOnName1,
( SELECT A.cost FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELECT
COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id )
= 1 ) AS AddOnCost1,
( SELECT A.name FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELECT
COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id )
= 2 ) AS AddOnName2,
( SELECT A.cost FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELECT
COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id )
= 2 ) AS AddOnCost2,
...
FROM data_branchDetails B
but it may be slow! This may be better!
SELECT B.name AS BranchName,
MAX(CASE WHEN S.Cnt = 1 THEN S.name END ) AS AddOnName1,
MAX(CASE WHEN S.Cnt = 1 THEN S.cost END ) AS AddOnCost1,
MAX(CASE WHEN S.Cnt = 2 THEN S.name END ) AS AddOnName2,
MAX(CASE WHEN S.Cnt = 2 THEN S.cost END ) AS AddOnCost2,
..
FROM data_branchDetails B
LEFT JOIN
( SELECT A.Branchid, A.name, A.Cost,
(SELECT COUNT(*) FROM data_branchAddOns C WHERE A.Branchid = C.Branchid AND
A.Id >= C.Id ) AS cnt
FROM data_branchAddOns A ) S ON S.Branchid = B.id
John
"Chris Ashley" wrote:

> I have two tables, data_branchAddOns:
> CREATE TABLE [dbo].[data_branchAddOns] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [branchId] [int] NOT NULL ,
> [name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [displayText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [cost] [decimal](5, 2) NOT NULL ,
> [ipt] [decimal](5, 2) NOT NULL ,
> [adminCharge] [decimal](5, 2) NOT NULL ,
> [deleted] [bit] NULL
> )
> data_branchDetails:
> CREATE TABLE [dbo].[data_branchDetails] (
> [id] [int] IDENTITY (1000, 1) NOT NULL ,
> [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [postcode] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [telephone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [fax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [website] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [b2b] [bit] NOT NULL ,
> [discountOffPlan] [decimal](5, 2) NOT NULL ,
> [discount1to4] [decimal](5, 2) NOT NULL ,
> [discount5to19] [decimal](5, 2) NOT NULL ,
> [discount20plus] [decimal](5, 2) NOT NULL ,
> [loading] [decimal](5, 2) NOT NULL ,
> [dateAdded] [datetime] NOT NULL ,
> [deleted] [bit] NOT NULL
> )
> One branch in branchDetails can have many branchAddOns. The key is ID
> in branchDetails, and the foreign key in branchAddOns is branchId.
>
> I have a problem in that I need to return a dataset in the form:
> BranchName AddOnName1 AddOnCost1 AddOnName2 AddOnCost2 AddOnName3
> AddOnCost3 ... to 5
> So basically I'm turning the table on its side and mapping the related
> records to be fields. Any idea how I can write a query to do this?
> There are only ever 5 'Add Ons' related to each 'Branch' so I am okay
> to map the field names in my procedure as I dont think it would be
> possible to have a dynamic number of fields returned?
> Thanks in advance
>|||That's really good. Thanks for the help guys.
I was worried I wasn't making any sense, as I am infinitely bad at
explaining SQL problems. :)
Cheers,
Chris
John Bell wrote:
> Hi Chris
> You may want to try something like:
> SELECT B.name AS BranchName,
> ( SELECT A.name FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELE
CT
> COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id
)
> = 1 ) AS AddOnName1,
> ( SELECT A.cost FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELE
CT
> COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id
)
> = 1 ) AS AddOnCost1,
> ( SELECT A.name FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELE
CT
> COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id
)
> = 2 ) AS AddOnName2,
> ( SELECT A.cost FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELE
CT
> COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id
)
> = 2 ) AS AddOnCost2,
> ...
> FROM data_branchDetails B
> but it may be slow! This may be better!
> SELECT B.name AS BranchName,
> MAX(CASE WHEN S.Cnt = 1 THEN S.name END ) AS AddOnName1,
> MAX(CASE WHEN S.Cnt = 1 THEN S.cost END ) AS AddOnCost1,
> MAX(CASE WHEN S.Cnt = 2 THEN S.name END ) AS AddOnName2,
> MAX(CASE WHEN S.Cnt = 2 THEN S.cost END ) AS AddOnCost2,
> ..
> FROM data_branchDetails B
> LEFT JOIN
> ( SELECT A.Branchid, A.name, A.Cost,
> (SELECT COUNT(*) FROM data_branchAddOns C WHERE A.Branchid = C.Branchid A
ND
> A.Id >= C.Id ) AS cnt
> FROM data_branchAddOns A ) S ON S.Branchid = B.id
> John
> "Chris Ashley" wrote:
>|||Hi Chris
The following details the things that will help you get quick answers from
the news groups http://www.aspfaq.com/etiquette.asp particularly
http://www.aspfaq.com/etiquette.asp?id=5006. Having the example data and the
expected results from that data also helps to remove any ambiguity that may
arise from just describing the problem in words.
John
"Chris Ashley" wrote:

> That's really good. Thanks for the help guys.
> I was worried I wasn't making any sense, as I am infinitely bad at
> explaining SQL problems. :)
> Cheers,
> Chris
> John Bell wrote:
>|||Hi John,
Thanks for that.
I've tried to amend the query like so:
SELECT B.name AS BranchName,
(SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
a.name = "homecover") AS AddOn1,
(SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
a.name = "policyfee") AS AddOn2,
(SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
a.name = "legalprotection") AS AddOn3
FROM data_branchDetails B
but I get the error:
Invalid column name 'homecover'.
Any ideas?
TIA,
Chris|||Hi Chris
Try using single quotes instead of the double ones that you have as you have
quoted identifiers set.
John
"Chris Ashley" wrote:

> Hi John,
> Thanks for that.
> I've tried to amend the query like so:
> SELECT B.name AS BranchName,
> (SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
> a.name = "homecover") AS AddOn1,
> (SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
> a.name = "policyfee") AS AddOn2,
> (SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
> a.name = "legalprotection") AS AddOn3
> FROM data_branchDetails B
> but I get the error:
> Invalid column name 'homecover'.
> Any ideas?
> TIA,
> Chris
>