Return a horizontal table for result

Has any one had luck writing a query that returns a result as follows:
CheckID PayAmtDeductAmt Tax Amt
1Admin200.00401k70.00FUTA13.00
1SAL1500.00FICA25.00
1MED30.00
1SOC107.00
I have three tables (Pay, Dedcut, and Tax) that are connected to a check table
Check Table
pcKey
pcEmployee
PayCheckDeduction
pcdKey
pcdPayCheck
pcdDeductionDescription
pcdAmount
etc...
the diffuculty I'm having is getting a horizontal return as describe above
for two or more tables connected to one table.
Please post the exact DDL (CREATE TABLE statements) for all of the tables
involved. It sounds like what you want is a pivot table, which can be
easily done.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
Has any one had luck writing a query that returns a result as follows:
CheckID Pay Amt Deduct Amt Tax Amt
1 Admin 200.00 401k 70.00 FUTA 13.00
1 SAL 1500.00 FICA 25.00
1 MED 30.00
1 SOC 107.00
I have three tables (Pay, Dedcut, and Tax) that are connected to a check
table
Check Table
pcKey
pcEmployee
PayCheckDeduction
pcdKey
pcdPayCheck
pcdDeductionDescription
pcdAmount
etc...
the diffuculty I'm having is getting a horizontal return as describe above
for two or more tables connected to one table.
|||Here you go Tom, I hope this helps
====================
DROP database zzPayroll
GO
CREATE database zzPayroll
GO
USE zzPayroll
GO
CREATE TABLE PayCheck (
pcKeyintNOT NULL ,
pcPayroll int NULL,
pcEmployee int NOT NULL
)
GO
CREATE TABLE PayType (
paytKeyintNOT NULL,
paytDescriptionvarchar(80)NOT NULL DEFAULT '',
-- 0 = Regular
-- 1 = Salary
-- 2 = Premium 1
-- 3 = Premium 2
-- 4 = Premium 3
-- 5 = Sick pay
-- 6 = Vacation pay
-- 7 = Other pay
-- 8 = Other in/out
-- 9 = Reimbursement
-- 10 = Accrual
paytType tinyint NOT NULL DEFAULT 0
)
GO
CREATE TABLE PayCheckTax (
pctKeyintNOT NULL ,
pctPayCheckintNOT NULL,
pctTaxTypeintNOT NULL,
pctAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE DeductionType (
dedtKeyintNOT NULL,
dedtDescriptionvarchar(80)NOT NULL DEFAULT ''
)
GO
CREATE TABLE TaxType (
taxtKeyintNOT NULL ,
taxtDescriptionvarchar(80)NOT NULL DEFAULT '',
-- 0 = Federal withholding
-- 1 = Federal unemployment
-- 2 = Earned income credit
-- 3 = Social Security
-- 4 = Medicare
-- 5 = State withholding,
-- 6 = State disability
-- 7 = State unemployment
-- 8 = Workers compensation
-- 9 = Local
-- 10 = Other
taxtTypetinyintNOT NULL DEFAULT 10
)
GO
CREATE TABLE PayCheckDeduction (
pcdKeyintNOT NULL,
pcdPayCheckintNOT NULL,
pcdDeductionTypeintNOT NULL,
pcdAmount decimal(19,2)NOT NULL DEFAULT 0
)
GO
CREATE TABLE Pays (
pKey int NOT NULL ,
pPayCheck int NULL,
pPayType int NULL,
pHours decimal(19,2) NOT NULL DEFAULT 0,
pAmount decimal(19,4) NULL
)
GO
INSERT PayType(paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
INSERT PayType(paytKey,paytDescription,paytType)
VALUES(4,'Vacation_Accrual',4)
INSERT DeductionType(dedtKey,dedtDescription) VALUES(0,'401k')
INSERT DeductionType(dedtKey,dedtDescription) VALUES(1,'Employee Medical')
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(0,1,0,40.00,900.00)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(1,1,2,2.00,60.00)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(3,1,4,8.00,0.00)
INSERT PayCheckDeduction(pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,0,72.00)
INSERT PayCheckDeduction(pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,1,25.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,0,25.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,1,100.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,2,35.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,4,75.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,5,105.00)
INSERT PayCheck(pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
================================================
"Tom Moreau" wrote:

> Please post the exact DDL (CREATE TABLE statements) for all of the tables
> involved. It sounds like what you want is a pivot table, which can be
> easily done.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
> Has any one had luck writing a query that returns a result as follows:
> CheckID Pay Amt Deduct Amt Tax Amt
> 1 Admin 200.00 401k 70.00 FUTA 13.00
> 1 SAL 1500.00 FICA 25.00
> 1 MED 30.00
> 1 SOC 107.00
> I have three tables (Pay, Dedcut, and Tax) that are connected to a check
> table
>
> Check Table
> pcKey
> pcEmployee
> PayCheckDeduction
> pcdKey
> pcdPayCheck
> pcdDeductionDescription
> pcdAmount
> etc...
> the diffuculty I'm having is getting a horizontal return as describe above
> for two or more tables connected to one table.
>
>
|||Looking at the original post, you have an assortment of results, with an
inconsistent number or columns in each row. What exactly are the business
requirements? Do you want a row for each type of pay - Reg, Overtime, etc.?
Do you want all deductions spread horizontally?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
Here you go Tom, I hope this helps
====================
DROP database zzPayroll
GO
CREATE database zzPayroll
GO
USE zzPayroll
GO
CREATE TABLE PayCheck (
pcKey int NOT NULL ,
pcPayroll int NULL,
pcEmployee int NOT NULL
)
GO
CREATE TABLE PayType (
paytKey int NOT NULL,
paytDescription varchar(80) NOT NULL DEFAULT '',
-- 0 = Regular
-- 1 = Salary
-- 2 = Premium 1
-- 3 = Premium 2
-- 4 = Premium 3
-- 5 = Sick pay
-- 6 = Vacation pay
-- 7 = Other pay
-- 8 = Other in/out
-- 9 = Reimbursement
-- 10 = Accrual
paytType tinyint NOT NULL DEFAULT 0
)
GO
CREATE TABLE PayCheckTax (
pctKey int NOT NULL ,
pctPayCheck int NOT NULL,
pctTaxType int NOT NULL,
pctAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE DeductionType (
dedtKey int NOT NULL,
dedtDescription varchar(80) NOT NULL DEFAULT ''
)
GO
CREATE TABLE TaxType (
taxtKey int NOT NULL ,
taxtDescription varchar(80) NOT NULL DEFAULT '',
-- 0 = Federal withholding
-- 1 = Federal unemployment
-- 2 = Earned income credit
-- 3 = Social Security
-- 4 = Medicare
-- 5 = State withholding,
-- 6 = State disability
-- 7 = State unemployment
-- 8 = Workers compensation
-- 9 = Local
-- 10 = Other
taxtType tinyint NOT NULL DEFAULT 10
)
GO
CREATE TABLE PayCheckDeduction (
pcdKey int NOT NULL,
pcdPayCheck int NOT NULL,
pcdDeductionType int NOT NULL,
pcdAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE Pays (
pKey int NOT NULL ,
pPayCheck int NULL,
pPayType int NULL,
pHours decimal(19,2) NOT NULL DEFAULT 0,
pAmount decimal(19,4) NULL
)
GO
INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
INSERT PayType (paytKey,paytDescription,paytType)
VALUES(4,'Vacation_Accrual',4)
INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee Medical')
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(0,1,0,40.00,900.00)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(1,1,2,2.00,60.00)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(3,1,4,8.00,0.00)
INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,0,72.00)
INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,1,25.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,0,25.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,1,100.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,2,35.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,4,75.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,5,105.00)
INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
================================================
"Tom Moreau" wrote:

> Please post the exact DDL (CREATE TABLE statements) for all of the tables
> involved. It sounds like what you want is a pivot table, which can be
> easily done.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
> Has any one had luck writing a query that returns a result as follows:
> CheckID Pay Amt Deduct Amt Tax Amt
> 1 Admin 200.00 401k 70.00 FUTA 13.00
> 1 SAL 1500.00 FICA 25.00
> 1 MED 30.00
> 1 SOC 107.00
> I have three tables (Pay, Dedcut, and Tax) that are connected to a check
> table
>
> Check Table
> pcKey
> pcEmployee
> PayCheckDeduction
> pcdKey
> pcdPayCheck
> pcdDeductionDescription
> pcdAmount
> etc...
> the diffuculty I'm having is getting a horizontal return as describe above
> for two or more tables connected to one table.
>
>
|||I am looking for a way show for a given check all the deductions, Pays, and
Taxes where the Deductions, Pays and Taxes appear in there own columns.
Chk Pay Ded Tax
1 x y z
1 null y z
1 null null z
"Tom Moreau" wrote:

> Looking at the original post, you have an assortment of results, with an
> inconsistent number or columns in each row. What exactly are the business
> requirements? Do you want a row for each type of pay - Reg, Overtime, etc.?
> Do you want all deductions spread horizontally?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
> Here you go Tom, I hope this helps
> ====================
> DROP database zzPayroll
> GO
> CREATE database zzPayroll
> GO
> USE zzPayroll
> GO
> CREATE TABLE PayCheck (
> pcKey int NOT NULL ,
> pcPayroll int NULL,
> pcEmployee int NOT NULL
> )
> GO
> CREATE TABLE PayType (
> paytKey int NOT NULL,
> paytDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Regular
> -- 1 = Salary
> -- 2 = Premium 1
> -- 3 = Premium 2
> -- 4 = Premium 3
> -- 5 = Sick pay
> -- 6 = Vacation pay
> -- 7 = Other pay
> -- 8 = Other in/out
> -- 9 = Reimbursement
> -- 10 = Accrual
> paytType tinyint NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE PayCheckTax (
> pctKey int NOT NULL ,
> pctPayCheck int NOT NULL,
> pctTaxType int NOT NULL,
> pctAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE DeductionType (
> dedtKey int NOT NULL,
> dedtDescription varchar(80) NOT NULL DEFAULT ''
> )
> GO
> CREATE TABLE TaxType (
> taxtKey int NOT NULL ,
> taxtDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Federal withholding
> -- 1 = Federal unemployment
> -- 2 = Earned income credit
> -- 3 = Social Security
> -- 4 = Medicare
> -- 5 = State withholding,
> -- 6 = State disability
> -- 7 = State unemployment
> -- 8 = Workers compensation
> -- 9 = Local
> -- 10 = Other
> taxtType tinyint NOT NULL DEFAULT 10
> )
> GO
> CREATE TABLE PayCheckDeduction (
> pcdKey int NOT NULL,
> pcdPayCheck int NOT NULL,
> pcdDeductionType int NOT NULL,
> pcdAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE Pays (
> pKey int NOT NULL ,
> pPayCheck int NULL,
> pPayType int NULL,
> pHours decimal(19,2) NOT NULL DEFAULT 0,
> pAmount decimal(19,4) NULL
> )
> GO
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
> INSERT PayType (paytKey,paytDescription,paytType)
> VALUES(4,'Vacation_Accrual',4)
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee Medical')
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(0,1,0,40.00,900.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(1,1,2,2.00,60.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(3,1,4,8.00,0.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
> VALUES(0,1,0,72.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
> VALUES(0,1,1,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,0,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,1,100.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,2,35.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,4,75.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,5,105.00)
> INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
> ================================================
> "Tom Moreau" wrote:
>
>
|||So, IOW, there is no direct correlation between a given deduction and a
given pay. Rather the correlation is between the deduction and the check
only. Is that right?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
I am looking for a way show for a given check all the deductions, Pays, and
Taxes where the Deductions, Pays and Taxes appear in there own columns.
Chk Pay Ded Tax
1 x y z
1 null y z
1 null null z
"Tom Moreau" wrote:

> Looking at the original post, you have an assortment of results, with an
> inconsistent number or columns in each row. What exactly are the business
> requirements? Do you want a row for each type of pay - Reg, Overtime,
> etc.?
> Do you want all deductions spread horizontally?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
> Here you go Tom, I hope this helps
> ====================
> DROP database zzPayroll
> GO
> CREATE database zzPayroll
> GO
> USE zzPayroll
> GO
> CREATE TABLE PayCheck (
> pcKey int NOT NULL ,
> pcPayroll int NULL,
> pcEmployee int NOT NULL
> )
> GO
> CREATE TABLE PayType (
> paytKey int NOT NULL,
> paytDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Regular
> -- 1 = Salary
> -- 2 = Premium 1
> -- 3 = Premium 2
> -- 4 = Premium 3
> -- 5 = Sick pay
> -- 6 = Vacation pay
> -- 7 = Other pay
> -- 8 = Other in/out
> -- 9 = Reimbursement
> -- 10 = Accrual
> paytType tinyint NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE PayCheckTax (
> pctKey int NOT NULL ,
> pctPayCheck int NOT NULL,
> pctTaxType int NOT NULL,
> pctAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE DeductionType (
> dedtKey int NOT NULL,
> dedtDescription varchar(80) NOT NULL DEFAULT ''
> )
> GO
> CREATE TABLE TaxType (
> taxtKey int NOT NULL ,
> taxtDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Federal withholding
> -- 1 = Federal unemployment
> -- 2 = Earned income credit
> -- 3 = Social Security
> -- 4 = Medicare
> -- 5 = State withholding,
> -- 6 = State disability
> -- 7 = State unemployment
> -- 8 = Workers compensation
> -- 9 = Local
> -- 10 = Other
> taxtType tinyint NOT NULL DEFAULT 10
> )
> GO
> CREATE TABLE PayCheckDeduction (
> pcdKey int NOT NULL,
> pcdPayCheck int NOT NULL,
> pcdDeductionType int NOT NULL,
> pcdAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE Pays (
> pKey int NOT NULL ,
> pPayCheck int NULL,
> pPayType int NULL,
> pHours decimal(19,2) NOT NULL DEFAULT 0,
> pAmount decimal(19,4) NULL
> )
> GO
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
> INSERT PayType (paytKey,paytDescription,paytType)
> VALUES(4,'Vacation_Accrual',4)
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee
> Medical')
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(0,1,0,40.00,900.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(1,1,2,2.00,60.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(3,1,4,8.00,0.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType,
> pcdAmount)
> VALUES(0,1,0,72.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType,
> pcdAmount)
> VALUES(0,1,1,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,0,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,1,100.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,2,35.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,4,75.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,5,105.00)
> INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
> ================================================
> "Tom Moreau" wrote:
>
>
|||Yes that is correct.
"Tom Moreau" wrote:

> So, IOW, there is no direct correlation between a given deduction and a
> given pay. Rather the correlation is between the deduction and the check
> only. Is that right?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
> I am looking for a way show for a given check all the deductions, Pays, and
> Taxes where the Deductions, Pays and Taxes appear in there own columns.
> Chk Pay Ded Tax
> 1 x y z
> 1 null y z
> 1 null null z
>
> "Tom Moreau" wrote:
>
>
|||Well, that is an odd requirement. The following may suffice:
select
pc.pcKey
, x.Type
, p.pAmount Pay
, pcd.pcdAmount Deduction
, pct.pctAmount Tax
from
(
select 0 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
) as x (Type)
cross
join
PayCheck pc
left
join
Pays p on p.pPayType = x.Type
and p.pPayCheck = pc.pcKey
left
join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
and pcd.pcdDeductionType = x.Type
left
join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
and pct.pctTaxType = x.Type
where not
(
p.pAmount is null
and pcd.pcdAmount is null
and pct.pctAmount is null
)
Note that you will get gaps, i.e. there may be nulls interspersed within a
column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
Yes that is correct.
"Tom Moreau" wrote:

> So, IOW, there is no direct correlation between a given deduction and a
> given pay. Rather the correlation is between the deduction and the check
> only. Is that right?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
> I am looking for a way show for a given check all the deductions, Pays,
> and
> Taxes where the Deductions, Pays and Taxes appear in there own columns.
> Chk Pay Ded Tax
> 1 x y z
> 1 null y z
> 1 null null z
>
> "Tom Moreau" wrote:
>
>
|||Thank you very much!
While working through this I found something similar using the union all but
this is more straight forward.
Thanks for you time on this!
"Tom Moreau" wrote:

> Well, that is an odd requirement. The following may suffice:
> select
> pc.pcKey
> , x.Type
> , p.pAmount Pay
> , pcd.pcdAmount Deduction
> , pct.pctAmount Tax
> from
> (
> select 0 union all
> select 1 union all
> select 2 union all
> select 3 union all
> select 4 union all
> select 5 union all
> select 6 union all
> select 7 union all
> select 8 union all
> select 9 union all
> select 10
> ) as x (Type)
> cross
> join
> PayCheck pc
> left
> join
> Pays p on p.pPayType = x.Type
> and p.pPayCheck = pc.pcKey
> left
> join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
> and pcd.pcdDeductionType = x.Type
> left
> join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
> and pct.pctTaxType = x.Type
> where not
> (
> p.pAmount is null
> and pcd.pcdAmount is null
> and pct.pctAmount is null
> )
> Note that you will get gaps, i.e. there may be nulls interspersed within a
> column.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
> Yes that is correct.
> "Tom Moreau" wrote:
>
>
|||My pleasure. Speaking of UNION ALL, have you considered FOR XML EXPLICIT?
select
1 as Tag
, NULL as Parent
, pcKey as [Check!1!CheckID]
, NULL as [Pay!2!Pay]
, NULL as [Deduction!3!Deduction]
, NULL as [Tax!4!Tax]
from
PayCheck
union all
select
2
, 1
, pPayCheck
, pAmount
, NULL
, NULL
from
Pays
union all
select
3
, 1
, pcdPayCheck
, NULL
, pcdAmount
, NULL
from
PayCheckDeduction
union all
select
4
, 1
, pctPayCheck
, NULL
, NULL
, pctAmount Tax
from
PayCheckTax
order by
[Check!1!CheckID]
for xml explicit
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:B68BD197-6E5A-4A8A-8A98-A81CBF848F46@.microsoft.com...
Thank you very much!
While working through this I found something similar using the union all but
this is more straight forward.
Thanks for you time on this!
"Tom Moreau" wrote:

> Well, that is an odd requirement. The following may suffice:
> select
> pc.pcKey
> , x.Type
> , p.pAmount Pay
> , pcd.pcdAmount Deduction
> , pct.pctAmount Tax
> from
> (
> select 0 union all
> select 1 union all
> select 2 union all
> select 3 union all
> select 4 union all
> select 5 union all
> select 6 union all
> select 7 union all
> select 8 union all
> select 9 union all
> select 10
> ) as x (Type)
> cross
> join
> PayCheck pc
> left
> join
> Pays p on p.pPayType = x.Type
> and p.pPayCheck = pc.pcKey
> left
> join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
> and pcd.pcdDeductionType = x.Type
> left
> join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
> and pct.pctTaxType = x.Type
> where not
> (
> p.pAmount is null
> and pcd.pcdAmount is null
> and pct.pctAmount is null
> )
> Note that you will get gaps, i.e. there may be nulls interspersed within a
> column.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
> Yes that is correct.
> "Tom Moreau" wrote:
>
>
sql