September 25, 2007 at 3:07 am
I have an Application which added the ability to create Custom Fields. They did this by adding a one-to-many table called custom_data. This table only contains 3 fields:
File_ID
field
value
File_ID is joined to the main tables
Field containsthe names of the Custom fields eg: und_01, und_02...und_10, srv_01, srv_02...srv10. Say I had 20 custom fields (10 for und_?? and 10 for srv_??)
Value is the data entered into the Custom Field. Boolean, ComboBox, Numeric ect
Here is an example of the table
File_ID Field Value
AAAA UND_01 N
AAAB SRV_01 09/25/2007
AABC UND_03 Some text data
AACA SRV_02 20.45
AADC UND_10 1
AAEF SRV_05 Y
AAEF UND_10 1
AAEF SRV_01 09/21/2007 (Multiple custom fields for same record)
AAFF UND_02 Some more text
The application does not add any custom field data in the Custom_Data table until data is entered into the Application's custom fields. If data is removed in the Application's Custom Field, the record is deleted from the Custom_data table.
I want to extract to excel and have all 20 fields in the header so that each record will contain data for those custom fields that have data and blank for those custom fields that do not contain data and therefore are "not" found in the Custom_Data table
Output
FILE_ID UND_01 UND_02 UND_03...UND_10 SRV_01 SRV_02 SRV_03...SRV_10
I have been trying to accomplish this using SQL Manager but if I linkmultiple copies of the Custom_data and use the following statement:
Select file_id, field, value
from custom_data
where (custom_data.field = 'UND_01') AND (custom_data2.field = 'UND_02)...TO 10 and then all 10 srv fields
This statment does not work because if there is not data in every field of every record, the "AND" fails and returns no records. If I use "OR" it does return the data but in multiple rows for the same record since there can be more than one custom field entered per record.
I am not that good outside of SQL Manager but there has got to be a way to write a scriptto make this process work. Any help on a solution would be appreciated...
Verl
September 25, 2007 at 5:33 am
So that basically means that you can have any datatype in this column [value]. Oh my god... What a genius it must have been to produce something like that.
What datatype did they use for column [value]?
I think that generally what you need is known as "crosstab" or "pivot" and you can find lots of links if you search this forum. There was even one very recent topic about this, it could be on the same page as your post. Mostly you can achieve what you need with lots of CASE statements.
But what if tomorrow or next month there are 23 custom "fields" instead of 20?
September 25, 2007 at 8:15 am
Verl,
This is the implementation that I can think of right away and may not take much time to produce and even create a template, assuming that you can use table variables for your solution. Using CASE statements may take more time, and judging from my past experience will require derived tables to get all the columns in one row. The following implementation is a quick and dirty way to get it done; you should be able to write code to generate it for later reusability.
Quick and Dirty Implementation
declare @table table
(
FileID varchar(10)
,field varchar(20)
,value varchar(100)
primary key
(
FileID
, field
)
)
declare @flatTable table
(
FileID varchar(10)
, UND_01 varchar(100)
, UND_02 varchar(100)
, UND_03 varchar(100)
, UND_04 varchar(100)
, UND_05 varchar(100)
, UND_06 varchar(100)
, UND_07 varchar(100)
, UND_08 varchar(100)
, UND_09 varchar(100)
, UND_10 varchar(100)
, SRV_01 varchar(100)
, SRV_02 varchar(100)
, SRV_03 varchar(100)
, SRV_04 varchar(100)
, SRV_05 varchar(100)
, SRV_06 varchar(100)
, SRV_07 varchar(100)
, SRV_08 varchar(100)
, SRV_09 varchar(100)
, SRV_10 varchar(100)
)
insert into @table
select
'AAAA'
, 'UND_01'
, 'N'
union all
select
'AAAB'
, 'SRV_01'
, '09/25/2007'
union all
select
'AAAB'
, 'SRV_02'
, '09/8/2007'
union all
select
'AABC'
, 'UND_01'
, 'Some text data'
union all
select
'AACA'
, 'SRV_02'
, '20.45'
union all
select
'AADC'
, 'UND_10'
, '1'
union all
select
'AAEF'
, 'SRV_05'
, 'Y'
union all
select
'AAFF'
, 'UND_02'
, 'Some more text'
INSERT INTO @FlatTable
(
FileID
, UND_01
)
select
FileId
, Value
from @table
where
Field = 'UND_01'
INSERT INTO @FlatTable
(
FileID
, UND_02
)
select
FileId
, Value
from @table
where
Field = 'UND_02'
INSERT INTO @FlatTable
(
FileID
, UND_03
)
select
FileId
, Value
from @table
where
Field = 'UND_03'
INSERT INTO @FlatTable
(
FileID
, UND_04
)
select
FileId
, Value
from @table
where
Field = 'UND_04'
INSERT INTO @FlatTable
(
FileID
, UND_05
)
select
FileId
, Value
from @table
where
Field = 'UND_05'
INSERT INTO @FlatTable
(
FileID
, UND_06
)
select
FileId
, Value
from @table
where
Field = 'UND_06'
INSERT INTO @FlatTable
(
FileID
, UND_07
)
select
FileId
, Value
from @table
where
Field = 'UND_07'
INSERT INTO @FlatTable
(
FileID
, UND_08
)
select
FileId
, Value
from @table
where
Field = 'UND_08'
INSERT INTO @FlatTable
(
FileID
, UND_09
)
select
FileId
, Value
from @table
where
Field = 'UND_09'
INSERT INTO @FlatTable
(
FileID
, UND_10
)
select
FileId
, Value
from @table
where
Field = 'UND_10'
INSERT INTO @FlatTable
(
FileID
, SRV_01
)
select
FileId
, Value
from @table
where
Field = 'SRV_01'
INSERT INTO @FlatTable
(
FileID
, SRV_02
)
select
FileId
, Value
from @table
where
Field = 'SRV_02'
INSERT INTO @FlatTable
(
FileID
, SRV_03
)
select
FileId
, Value
from @table
where
Field = 'SRV_03'
INSERT INTO @FlatTable
(
FileID
, SRV_04
)
select
FileId
, Value
from @table
where
Field = 'SRV_04'
INSERT INTO @FlatTable
(
FileID
, SRV_05
)
select
FileId
, Value
from @table
where
Field = 'SRV_05'
INSERT INTO @FlatTable
(
FileID
, SRV_06
)
select
FileId
, Value
from @table
where
Field = 'SRV_06'
INSERT INTO @FlatTable
(
FileID
, SRV_07
)
select
FileId
, Value
from @table
where
Field = 'SRV_07'
INSERT INTO @FlatTable
(
FileID
, SRV_08
)
select
FileId
, Value
from @table
where
Field = 'SRV_08'
INSERT INTO @FlatTable
(
FileID
, SRV_09
)
select
FileId
, Value
from @table
where
Field = 'SRV_09'
INSERT INTO @FlatTable
(
FileID
, SRV_10
)
select
FileId
, Value
from @table
where
Field = 'SRV_10'
select * FROM @TABLE
select * from @flatTable
** NOTE: Please format SQL before running, I am experiencing problems editing posts this morning, the formatting is not working as it did late last night.
Regards,
Wameng Vang
MCTS
September 25, 2007 at 9:36 pm
Yes, the plot thickens. They give me Data Type options of Text, Numeric, Combo Box (Text), Boolean, Date, and Date Time.
I was happy to get Custom fields until I found out how much of a pain they are to access outside the application.
It gets worse since the Combo Box dropdown return "codes" instead of Text and I have to link to another table to pickup the Text.
Pivot table may be the way to go and I will check that out.
Thanks!!!
Verl
September 25, 2007 at 9:44 pm
mengus,
Thanks a lot for your response. I will try your suggestion and see how it works.
I really appreciate the time you spent on this problem...
Verl
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply