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

Debra Smith wrote:
Show quoteHide quoteI 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) ;

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


David Portas wrote:
Show quoteHide quote

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;

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.

Show quoteHide quote

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?

--CELKO-- wrote:
Show quoteHide quote

Debra Smith via SQLMonster.com (u18862@uwe) writes:
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       
    ....

Toughy Normalization Problem
Timeout expired - on a simple SqlDataReader:Read() call
Is it possible to join two tables in a View/Query where the Second Table is based on a Name in the F
Primary Key Question
OBJECTPROPERTY problem