sql server programming: [quoted text, click to view]

I don't think you'll need a CASE expression. More like a UNION if I've
understood you correctly:

SELECT unit, account, account_desc, pp
FROM
(SELECT cr_unit_01 + db_unit_01, account, account_desc, 1
FROM your_table
UNION ALL
SELECT cr_unit_02 + db_unit_02, account, account_desc, 2
FROM your_table
UNION ALL
SELECT cr_unit_03 + db_unit_03, account, account_desc, 3
FROM your_table
) AS T (unit, account, account_desc, pp) ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

This looks like a violation of 1NF. if the unit_01, unit_02, and
unit_03 are a repeated group.

CREATE TABLE Accounts
(account_nbr INTEGER NOT NULL PRIMARY KEY,
account_desc CHAR(15) NOT NULL);

CREATE TABLE Ledger
(account_nbr INTEGER NOT NULL
REFERENCES Accounts (account_nbr),
ledger_amt DECIMAL (12,2) NOT NULL,
unit_nbr INTEGER NOT NULL,
..);

Then just do a simple summation

SELECT account_nbr,
SUM(CASE WHEN unit_nbr = 1 THEN ledger_amt ELSE 0.00 END) AS
unit_1,
SUM(CASE WHEN unit_nbr = 2 THEN ledger_amt ELSE 0.00 END) AS
unit_2,
SUM(CASE WHEN unit_nbr = 3 THEN ledger_amt ELSE 0.00 END) AS
unit_3
FROM Ledger;

Using SQL Server 2000 to create the view. Microsoft SQL Enterprise Manager,
Microsoft Corporation. Version: 8.0

I have a table that has the following types of columns all in a single row:

Cr_Unit_01, Db_Unit_01, Cr_Unit_02, Db_Unit_02, Cr_Unit_03, Db_Unit_03,
account, account_desc

I need to capture the data from the table into a View as follows:
cr_unit_01 + db_unit_01 AS unit_01, account, account_desc, 1 as pp1 (row
1)
cr_unit_02 + db_unit_02 AS unit_02, account, account_desc, 2 as PP2 (row 2)

cr_unit_03 + db_unit_02 AS unit_03, account, account_desc, 3 as PP3 (row 3)


Can anyone show me how I would create a view using the case statement to
create each column and row as defined above.

Thanks for any help!!!!

--

Repeating groups may indicate a poor design, but their existence in a
relation schema doesn't violate 1NF. A relation schema is in 1NF if and
only if for every legal relation value each cell has exactly one value.

[quoted text, click to view]

I'll try to explain the table that I'm using to create my View from.
Company smalint(2)
Fiscal_Year smallint(2)
Budget_Nbr smallint(2)
Acct_Unit char(15)
Account int(4)
db_units_01 decimal(9)
db_units_02 decimal(9)
db_units_03 decimal(9)
db_units_04 decimal(9)
cr_units_01 decimal(9)
cr_units_02 decimal(9)
cr_units_03 decimal(9)
cr_units_04 decimal(9)


Currently my view that gives me all of the data in a single row is:

SELECT dbo.FBDETAIL.COMPANY, dbo.FBDETAIL.FISCAL_YEAR, dbo.FBDETAIL.
BUDGET_NBR,
dbo.FBDETAIL.ACCT_UNIT, dbo.FBDETAIL.ACCOUNT,
dbo.FBDETAIL.DB_UNITS_01 + dbo.FBDETAIL.CR_UNITS_01 AS
Untbud1,
dbo.FBDETAIL.DB_UNITS_02 + dbo.FBDETAIL.CR_UNITS_02 AS
Untbud2,
dbo.FBDETAIL.DB_UNITS_03 + dbo.FBDETAIL.CR_UNITS_03 AS
Untbud3,
dbo.FBDETAIL.DB_UNITS_04 + dbo.FBDETAIL.CR_UNITS_04 AS
Untbud4,
FROM dbo.FBDETAIL
WHERE (dbo.FBDETAIL.ACCT_UNIT >= '6000') AND (dbo.FBDETAIL.ACCT_UNIT <=
'9990') AND
(dbo.FBDETAIL.ACCOUNT >= 51110 AND
dbo.FBDETAIL.ACCOUNT <= 51140 OR
dbo.FBDETAIL.ACCOUNT >= 51210 AND dbo.FBDETAIL.ACCOUNT <=
51240 OR
dbo.FBDETAIL.ACCOUNT >= 51310 AND dbo.FBDETAIL.ACCOUNT =
51340) AND
(dbo.FBDETAIL.FISCAL_YEAR = '2006')
ORDER BY dbo.FBDETAIL.COMPANY, dbo.FBDETAIL.FISCAL_YEAR, dbo.FBDETAIL.
BUDGET_NBR,
dbo.FBDETAIL.ACCT_UNIT, dbo.FBDETAIL.ACCOUNT

The results are:
Company Fiscal_year Budget_Nbr Acct_unit Account UntBud1 UntBud2
UntBud3 UntBud4
1 2006 100 6000 51110
5990.81 5065.65 5664 5367.09
1 2006 100 6000 51210
104.23 94.14 104.23 100.87
1 2006 100 6010 51110
3910.68 3848.23 3740.11 3668.4
1 2006 100 6010 51210
30.03 27.13 30.03 29.06

The results I need to see are:

Company Fiscal_year Budget_Nbr AcctUnit Account UntBud1 PayPd
1 2006 100 6000 51110
5990.81 1
1 2006 100 6000 51110
5065.65 2
1 2006 100 6000 51110
5664 3
1 2006 100 6000 51110
5367.09 4
1 2006 100 6000 51120
104.23 1
1 2006 100 6000 51120
94.14 2
1 2006 100 6000 51120
104.23 3
1 2006 100 6000 51120
100.87 4

Does this help everyone understand what I'm doing? Also, when you say UNION,
are you meaning on the same table over and over again?

Thanks,

Debbie S


[quoted text, click to view]

--
Debbie Smith

Message posted via SQLMonster.com

I'm sorry for not including everything. I kind of new to asking questions
and need to learn what I need to include in my posts.

I'll try to explain the table that I'm using to create my View from.
Company smalint(2)
Fiscal_Year smallint(2)
Budget_Nbr smallint(2)
Acct_Unit char(15)
Account int(4)
db_units_01 decimal(9)
db_units_02 decimal(9)
db_units_03 decimal(9)
db_units_04 decimal(9)
cr_units_01 decimal(9)
cr_units_02 decimal(9)
cr_units_03 decimal(9)
cr_units_04 decimal(9)

Currently my view that gives me all of the data in a single row is:

SELECT dbo.FBDETAIL.COMPANY, dbo.FBDETAIL.FISCAL_YEAR, dbo.FBDETAIL.
BUDGET_NBR,
dbo.FBDETAIL.ACCT_UNIT, dbo.FBDETAIL.ACCOUNT,
dbo.FBDETAIL.DB_UNITS_01 + dbo.FBDETAIL.CR_UNITS_01 AS
Untbud1,
dbo.FBDETAIL.DB_UNITS_02 + dbo.FBDETAIL.CR_UNITS_02 AS
Untbud2,
dbo.FBDETAIL.DB_UNITS_03 + dbo.FBDETAIL.CR_UNITS_03 AS
Untbud3,
dbo.FBDETAIL.DB_UNITS_04 + dbo.FBDETAIL.CR_UNITS_04 AS
Untbud4,
FROM dbo.FBDETAIL
WHERE (dbo.FBDETAIL.ACCT_UNIT >= '6000') AND (dbo.FBDETAIL.ACCT_UNIT <=
'9990') AND
(dbo.FBDETAIL.ACCOUNT >= 51110 AND
dbo.FBDETAIL.ACCOUNT <= 51140 OR
dbo.FBDETAIL.ACCOUNT >= 51210 AND dbo.FBDETAIL.ACCOUNT <=
51240 OR
dbo.FBDETAIL.ACCOUNT >= 51310 AND dbo.FBDETAIL.ACCOUNT =
51340) AND
(dbo.FBDETAIL.FISCAL_YEAR = '2006')
ORDER BY dbo.FBDETAIL.COMPANY, dbo.FBDETAIL.FISCAL_YEAR, dbo.FBDETAIL.
BUDGET_NBR,
dbo.FBDETAIL.ACCT_UNIT, dbo.FBDETAIL.ACCOUNT

The results are:
Company Fiscal_year Budget_Nbr Acct_unit Account UntBud1
UntBud2UntBud3 UntBud4
1 2006 100 6000 51110
5990.81 5065.65 5664 5367.09
1 2006 100 6000 51210
104.23 94.14 104.23 100.87
1 2006 100 6010 51110
3910.68 3848.23 3740.11 3668.4
1 2006 100 6010 51210
30.03 27.13 30.03 29.06

The results I need to see are:

Company Fiscal_year Budget_Nbr AcctUnit Account UntBud1 PayPd
1 2006 100 6000 51110
5990.81 1
1 2006 100 6000 51110
5065.65 2
1 2006 100 6000 51110
5664 3
1 2006 100 6000 51110
5367.09 4
1 2006 100 6000 51120
104.23 1
1 2006 100 6000 51120
94.14 2
1 2006 100 6000 51120
104.23 3
1 2006 100 6000 51120
100.87 4

Does this help everyone understand what I'm doing?

[quoted text, click to view]

--
Debbie Smith

Message posted via SQLMonster.com

Debra Smith via SQLMonster.com (u18862@uwe) writes:
[quoted text, click to view]

Indeed a CASE expression what you need. That an a derived tables with
numbers:

SELECT f.COMPANY, f.FISCAL_YEAR, f.BUDGET_NBR, f.ACCT_UNIT, f.ACCOUNT,
CASE n.n
WHEN 1 THEN f.DB_UNITS_01 + f.CR_UNITS_01
WHEN 2 THEN f.DB_UNITS_02 + f.CR_UNITS_02 ,
WHEN 3 THEN f.DB_UNITS_03 + f.CR_UNITS_03,
WHEN 4 THEN f.DB_UNITS_04 + f.CR_UNITS_04 ,
END AS Untbud, n.n AS Payid
FROM dbo.FBDETAIL f
CROSS JOIN (SELECT n = 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4) AS n
WHERE (ACCT_UNIT >= '6000') AND (ACCT_UNIT <= '9990') AND
....




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at