October 23, 2020 at 2:50 pm
I have two tables called SubAccounts and AccountCategories. Table definitions are as follows:
CREATE TABLE [dbo].[SubAccounts](
[Sub] [char](3) NOT NULL,
[Descr] [varchar](50) NOT NULL,
[Sort] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[AccountCategories](
[Category] [varchar](50) NOT NULL,
[AccountList] [varchar](255) NOT NULL,
[Sort] [int] NOT NULL
) ON [PRIMARY]
The Subaccounts table has just that - a list of Subaccounts and their descriptions (1 per row).
The AccountCategories table has a little more involved data. It contains a description (category description) and a SQL-type clause that would be used to get data for that category.
Sample data is as follows:
Sample data is as follows:
INSERT INTO SubAccounts
(Sub, Descr, Sort)
SELECT '000','SUB DESC 1','0' UNION ALL
SELECT '001','SUB DESC 2','1' UNION ALL
SELECT '002','SUB DESC 3','2' UNION ALL
SELECT '003','SUB DESC 4','3' UNION ALL
SELECT '004','SUB DESC 5','4' UNION ALL
SELECT '005','SUB DESC 6','5' UNION ALL
SELECT '006','SUB DESC 7','6' UNION ALL
SELECT '007','SUB DESC 8','7' UNION ALL
SELECT '008','SUB DESC 9','8' UNION ALL
SELECT '009','SUB DESC 10','9'
INSERT INTO AccountCategories
(Category,AccountList, Sort)
SELECT 'Description 1',' =''ACCT1''','0' UNION ALL
SELECT 'Description 2','BETWEEN ''ACCT2'' AND ''ACCT4''','1' UNION ALL
SELECT 'Description 3','IN (''ACCT5'',''ACCT6'')','2' UNION ALL
SELECT 'Description 4',' =''ACCT7''','3' UNION ALL
SELECT 'Description 5',' =''ACCT8''','4'
The result I'm trying to achive would look like the output of this SQL query:
CREATE TABLE [dbo].[SampleResults](
[Account] [varchar](50) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO SampleResults VALUES ('Description 1','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''000''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''001''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''002''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''003''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''004''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''005''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''006''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''007''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''008''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''009''>')
INSERT INTO SampleResults VALUES ('Description 2','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''000''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''001''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''002''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''003''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''004''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''005''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''006''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''007''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''008''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''009''>')
INSERT INTO SampleResults VALUES ('Description 3','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''000''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''001''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''002''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''003''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''004''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''005''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''006''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''007''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''008''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''009''>')
INSERT INTO SampleResults VALUES ('Description 4','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''000''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''001''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''002''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''003''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''004''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''005''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''006''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''007''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''008''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''009''>')
INSERT INTO SampleResults VALUES ('Description 5','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''000''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''001''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''002''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''003''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''004''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''005''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''006''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''007''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''008''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''009''>')
SELECT * FROM SampleResults
The values in the SUB DESC 1 through SUB DESC 10 columns explain how I want to populate that data.
The DATA would come from a third table by SQL statement (yet to be developed). That's where the Account column is (and there's obviously a Sub column as well).
I don't even know where to start with something like this. How would I be best to go about creating this, given what I have?
October 23, 2020 at 3:51 pm
This table structure is, and I don't use the word lightly, horrendous.
Is it set in stone, or is there the potential to remodel this into something which follows basic relational principles?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 23, 2020 at 3:54 pm
Potentially. What suggestions do you have?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply