Normalized Vs Denormalized Design

Do you mean convert the verticle design into an xml format to bind to the grid?

I would still have to construct the XML file through itereation building the horizontal result set or am I missing something?

<<The problem with this design is speed. >>
Do not mix logical and physical design, else you will end doing *both* wrong.  In any case, a solution that trades in integrity for performance  is a poor solution.  Keep in mind that response time performance is a physical consideration only that can effectively be dealt throuh physical means (you can for instance pin the data required for making merges into memory).  If your logical design is fully normalized, the memory footprint can be kept to a minimum while guaranteeing that you have both integrity *and* response time...

Oh yeah and just forget about XML.  It will just make things worse...

HTH

Racimo: I don't really understand your suggestion.  If my DB is normalized (values are stored horizontal) then I'll have to iterate through the verticle result set to generate a horizontal set suitable to bind to a grid.. This takes time... Could you please elaborate?

I'm sorry, I meant to say

Racimo: I don't really understand your suggestion.  If my DB is normalized (values are stored VERTICLY) then I'll have to iterate through the verticle result set to generate a horizontal set suitable to bind to a grid.. This takes time... Could you please elaborate?

Thanks

OK, so perhaps I didn't make my question clear.

I understand that a verticle (Normalized) solution is superior because of data integrity.
It doesn't take much time at all to retrieve lots and lots of verticle values.

Currently, I'm returning many verticle rows of data in which in VB i have to itereate through each, building my horizontal result set.  This is SLOW and unacceptable.

Through more research, It seems that the results can be returned in SQL the way that I want them by using a "PIVOT with dynamic columns".  Everyone online explains how to do this in SQL 2005.

Can anyone please tell me if a "PIVOT with dynamic columns" is possible in SQL server 2000 and if so please provide an example.

Mods:  please let me know if i need to create another question since my question has changed a bit.

Thanks.

<<I'll have to iterate through the verticle result set to generate a horizontal set suitable to bind to a grid..>>
Database design do not follow the same rules thant user interface layout.  Database design involves a totally different set of rules.

<<This takes time... Could you please elaborate?>>
There is a common confusion in database design that involves believing that normalization is about building tables but that is not the case.  Normalization is *not* about storing values *vertically* or in a way to match or not a visual interface layout.    Normalization is a *logical* process for optimizing the encoding of information into a the database.  The design effort should be separated between building a logical model then a physical implementation out from it.  

<<This takes time... Could you please elaborate?>>
Well I am sorry if you find this is not easier.  Anybody who would tell you that sound database design is a trivial task simply has no clue what he/she is talking about.  From the tenure of your question, I'd recommend doing some further reading to see what I am refering to.  Here are some pointers.

This book should help you get started...

http://www.amazon.com/dp/0201485559?tag=databasede095-20&camp=14573&creative=327641&linkCode=as1&creativeASIN=0201485559&adid=0QMYMK7JRWFK0YDAVQPP&

HTH

Is it possible to do a Pivot with dynamic columns in MS-SQL 2000?

If so could someone please provide an example on how to do this?

Thanks.

<<Is it possible to do a Pivot with dynamic columns in MS-SQL 2000?>>
No.  You need to do some coding for that.

Do you mean that I need to do some TSQL to make this happen or in my programming language of choice.

if it's possible with TSQL an example is what I'm looking for.

Yes it is possible, have a look at the procedure below.

But first we create some test data - essentially two templates one for contacts the other for business contacts with appropriately named columns of name phone modile and address.

Noticed that there are some interesting aspects of the design that I might not have quite captured correctly, but you can see what I have done in recreating your tables with the EE_ prefix (for your testing, remove that EE_ prefix within the stored procedure ie a global change of 'EE_' to '' within the procedure).

-- step 1 create the test tables
 
create Table EE_Template
(TemplateID int identity primary key clustered,
 TemplateDisplayName varchar(100))
 
create Table EE_Fields
(FieldID int identity primary key clustered,
 FieldDisplayName varchar(100),
 TemplateID int)
 
create Table EE_Records
(RecordID int identity primary key clustered,
 FolderID int)
 
create Table EE_DataValues
(ValueID int identity primary key clustered,
 TemplateID int,
 FieldID int,
 FieldValue varchar(100),
 RecordID int)
 
GO
 
-- step 2 create the first template - "contacts" and populate with two examples
 
insert ee_template (TemplateDisplayName) values ('My Contacts')
 
insert ee_fields (FieldDisplayName,TemplateID) values ('Name',1)
insert ee_fields (FieldDisplayName,TemplateID) values ('Phone',1)
insert ee_fields (FieldDisplayName,TemplateID) values ('Mobile',1)
insert ee_fields (FieldDisplayName,TemplateID) values ('Address',1)
 
insert ee_records (folderid) values (1)
 
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (1,1,'Mark',1)
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (1,2,'+61 555666777',1)
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (1,3,'+61 777666555',1)
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (1,4,'Somewhere Downunder',1)
 
insert ee_records (folderid) values (2)
 
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (1,1,'Jon',2)
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (1,2,'+61 111222333',2)
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (1,3,'+61 333222111',2)
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (1,4,'Somewhere Also ownunder',2)
 
-- step 3 create another template "Business Contacts" and populate also with two examples
 
insert ee_template (TemplateDisplayName) values ('My Business Contacts')
 
insert ee_fields (FieldDisplayName,TemplateID) values ('Business Name',2)
insert ee_fields (FieldDisplayName,TemplateID) values ('Work Phone',2)
insert ee_fields (FieldDisplayName,TemplateID) values ('Mobile',2)
insert ee_fields (FieldDisplayName,TemplateID) values ('Company Address',2)
 
insert ee_records (folderid) values (3)
 
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (2,5,'Business Mark',3)
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (2,6,'+61 555666777',3)
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (2,7,'+61 777666555',3)
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (2,8,'Business Downunder',3)
 
insert ee_records (folderid) values (4)
 
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (2,5,'Business Jon',4)
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (2,6,'+61 111222333',4)
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (2,7,'+61 333222111',4)
insert ee_datavalues (templateid, Fieldid, FieldValue, recordid) values (2,8,'Business Also ownunder',4)
 
-- step 4 just to check, run a "normal" vertical query
 
select TemplateDisplayName as table_name, R.Recordid as row_no, FieldDisplayName as column_name, FieldValue
from ee_datavalues D
inner join ee_template T on T.templateid = D.templateid
inner join ee_records R on R.recordid = D.recordid
inner join ee_fields F on F.templateid = D.templateid and F.fieldid = D.fieldid
 
-- step 5 - the real stuff - remember we are using the prefix "EE_" which you will need to change (remove) after testing
 
-- OK. Finally. Now for the procedure. We simply tie it down to the template level - can easily add in other criteria
 
create procedure usp_pivot_eav (@templateid int)
as
begin
 
declare @c varchar(8000)
select @c = isnull(@c + ',','') + 'max([' + FieldDisplayName + ']) as [' + FieldDisplayName + ']'  from ee_fields where templateid = @templateid
--print @c
 
declare @s varchar(8000)
select @s = isnull(@S + ',','') + 'case when fielddisplayname = '''+ FieldDisplayName + ''' then fieldValue else '''' end as [' + FieldDisplayName + ']'  from ee_fields F where F.templateid = @templateid
--print @s
 
declare @r varchar(80)
set @r = convert(varchar,@templateid)
 
exec ('Select table_name, row_no, '+@c+'
from (
select TemplateDisplayName as table_name, R.Recordid as row_no, '+@s+'
from ee_datavalues D
inner join ee_template T on T.templateid = D.templateid
inner join ee_records R on R.recordid = D.recordid
inner join ee_fields F on F.templateid = D.templateid and F.fieldid = D.fieldid
where T.templateid = '+@r + ') s
Group by table_name,Row_no')
 
end
 
GO
 
-- now to test our procedure 
 
exec usp_pivot_eav 1     -- a list of contacts
 
exec usp_pivot_eav 2     -- a list of business contacts
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:

mark_wills: The code looks very iinteresting.

I plan to port and implement ASAP.

I'lll let you know if it works and assign points accordingly.

Thanks again for all replies.

Hope it works well for you...

mark wills:

That is perfect.. one question though.  How would I modifiy the procedure to show me all of the records for a particular TemplateID AND FolderID?  This solution is so awesome.

You will definitley be getting the points for this one.

Thanks again.

Just to be clear

I want to see all records that are of TemplateID 'X' that are found in Folder "x"

Thanks.

OK.. when i first asked this question, I illustrated the tables a bit different than they really are (the names we use are confusing, its an old database).  Your example worked perfect, however, when I updated it to use my tables/columns, I'm getting errors.  Here is my updated procedure.  Any ideas?

create procedure usp_pivot_eav (@templateid varchar(250))
as
begin
 
declare @c varchar(8000)
select @c = isnull(@c + ',','') + 'max([' + Description + ']) as [' + Description + ']'  from ieuser.DataTypes where SystemCode = @templateid
--print @c
 
declare @s varchar(8000)
select @s = isnull(@S + ',','') + 'case when Description = '''+ Description + ''' then [value] else '''' end as [' + Description + ']'  from ieuser.DataTypes F where F.SystemCode = @templateid
--print @s
 
declare @r varchar(80)
set @r = convert(varchar,@templateid)
 
exec ('Select table_name, row_no, '+@c+'
from (
select Description as table_name, R.MasterKey as row_no, '+@s+'
from ieuser.data D
inner join ieuser.systemcodes T on T.SystemCode = D.SystemCode
inner join ieuser.masterkey R on R.masterkey = D.masterkey
inner join ieuser.DataTypes F on F.SystemCode = D.SystemCode and F.dataid = D.dataid
where T.SystemCode = '+@r + ') s
Group by table_name,Row_no')
 
end
 
GO

Well, it kinda looks OK. What is the error ?

Can always run it just as a straight query and see what happens e.g.

declare @templateid varchar(250)
set @templateid = 'put a value here'

declare @c varchar(8000)
select @c = isnull(@c + ',','') + 'max([' + Description + ']) as [' + Description + ']' from ieuser.DataTypes where SystemCode = @templateid
print @c

declare @s varchar(8000)
select @s = isnull(@S + ',','') + 'case when Description = '''+ Description + ''' then [value] else '''' end as [' + Description + ']' from ieuser.DataTypes F where F.SystemCode = @templateid
print @s

declare @r varchar(80)
set @r = convert(varchar,@templateid)
declare @sql varchar(8000)

set @sql = 'Select table_name, row_no, '+@c+'
from (
select Description as table_name, R.MasterKey as row_no, '+@s+'
from ieuser.data D
inner join ieuser.systemcodes T on T.SystemCode = D.SystemCode
inner join ieuser.masterkey R on R.masterkey = D.masterkey
inner join ieuser.DataTypes F on F.SystemCode = D.SystemCode and F.dataid = D.dataid
where T.SystemCode = '+@r + ') s
Group by table_name,Row_no'

print @sql

--exec(@sql)


Now those print statements should reveal the actual code to be executed. You can even copy the results of : print @sql from the message tab and run it directly as a query.

here are the results that i get

I see that the print SQL shows that the query is not being completly output but i'm not sure why.. any ideas?

max([SO Number]) as [SO Number],max([Date Created]) as [Date Created],max([Time Created]) as [Time Created],max([Date RFQ Submitted to Product Support]) as [Date RFQ Submitted to Product Support],max([Date RFQ Submitted to Production Control]) as [Date RFQ Submitted to Production Control],max([Date of RFQ Product Support Decision]) as [Date of RFQ Product Support Decision],max([Date RFQ Submitted to Cust]) as [Date RFQ Submitted to Cust],max([Gov Sol Number]) as [Gov Sol Number],max([RFQ Status]) as [RFQ Status],max([Program]) as [Program],max([Customer Part Number]) as [Customer Part Number],max([Quote Value (Only For Product Support)]) as [Quote Value (Only For Product Support)],max([Date RFQ Status Set to Part Number Issues]) as [Date RFQ Status Set to Part Number Issues],max([Time RFQ Status Set to Part Number Issues]) as [Time RFQ Status Set to Part Number Issues],max([Date RFQ Status Set to Pending Customer Creation in SAP]) as [Date RFQ Status Set to Pending Customer Creation in SAP],max([Time RFQ Status Set to Pending Customer Creation in SAP]) as [Time RFQ Status Set to Pending Customer Creation in SAP],max([Date RFQ Status Set to Bid/No Bid Evaluation]) as [Date RFQ Status Set to Bid/No Bid Evaluation],max([Time RFQ Status Set to Bid/No Bid Evaluation]) as [Time RFQ Status Set to Bid/No Bid Evaluation],max([Date RFQ Status Set to QR Prep]) as [Date RFQ Status Set to QR Prep],max([Time RFQ Status Set to QR Prep]) as [Time RFQ Status Set to QR Prep],max([Date RFQ Status Set to Pricing]) as [Date RFQ Status Set to Pricing],max([Time RFQ Status Set to Pricing]) as [Time RFQ Status Set to Pricing],max([Pricing Approved by Product Support]) as [Pricing Approved by Product Support],max([Time RFQ Submitted to Production Control]) as [Time RFQ Submitted to Production Control],max([Time RFQ Submitted to Product Support]) as [Time RFQ Submitted to Product Support],max([Time of RFQ Product Support Decision]) as [Time of RFQ Product Support Decision],max([Date RFQ Status Set to Pre-Submittal]) as [Date RFQ Status Set to Pre-Submittal],max([Time RFQ Status Set to Pre-Submittal]) as [Time RFQ Status Set to Pre-Submittal],max([Time RFQ Submitted to Cust]) as [Time RFQ Submitted to Cust],max([Date of Disposition]) as [Date of Disposition],max([Time of Disposition]) as [Time of Disposition],max([Commercial/Military]) as [Commercial/Military],max([PO Number]) as [PO Number],max([Date of Complaint]) as [Date of Complaint],max([Complaint]) as [Complaint],max([Complaint Status]) as [Complaint Status],max([Complaint Assigned To]) as [Complaint Assigned To],max([Complainant Phone Number]) as [Complainant Phone Number],max([PO Rev Number]) as [PO Rev Number],max([PO Rev Date]) as [PO Rev Date],max([SO Updated]) as [SO Updated],max([Bid/No Bid Decision by Product Support]) as [Bid/No Bid Decision by Product Support],max([Complainant Email Address]) as [Complainant Email Address],max([Work Unit ID]) as [Work Unit ID],max([Assigned To]) as [Assigned To],max([Complainant Name]) as [Complainant Name],max([SO Creation Date]) as [SO Creation Date],max([RFQ Date Created]) as [RFQ Date Created],max([Customer]) as [Customer],max([Customer Contact]) as [Customer Contact],max([HRT Part Number]) as [HRT Part Number],max([Comments]) as [Comments],max([Status]) as [Status],max([RFQ Number]) as [RFQ Number],max([RFQ Work Unit ID]) as [RFQ Work Unit ID],max([OEM/After Market]) as [OEM/After Market],max([RFQ Date]) as [RFQ Date],max([Quote/QR Number]) as [Quote/QR Number],max([Owned By]) as [Owned By],max([Quote Expiration Date]) as [Quote Expiration Date],max([RFQ Customer Due Date]) as [RFQ Customer Due Date],max([Serial Number]) as [Serial Number],max([RFQ Time Created]) as [RFQ Time Created],max([Date Complaint Closed]) as [Date Complaint Closed],max([Time of Complaint]) as [Time of Complaint],max([Time Complaint Closed]) as [Time Complaint Closed],max([New Business?]) as [New Business?]
case when Description = 'SO Number' then [value] else '' end as [SO Number],case when Description = 'Date Created' then [value] else '' end as [Date Created],case when Description = 'Time Created' then [value] else '' end as [Time Created],case when Description = 'Date RFQ Submitted to Product Support' then [value] else '' end as [Date RFQ Submitted to Product Support],case when Description = 'Date RFQ Submitted to Production Control' then [value] else '' end as [Date RFQ Submitted to Production Control],case when Description = 'Date of RFQ Product Support Decision' then [value] else '' end as [Date of RFQ Product Support Decision],case when Description = 'Date RFQ Submitted to Cust' then [value] else '' end as [Date RFQ Submitted to Cust],case when Description = 'Gov Sol Number' then [value] else '' end as [Gov Sol Number],case when Description = 'RFQ Status' then [value] else '' end as [RFQ Status],case when Description = 'Program' then [value] else '' end as [Program],case when Description = 'Customer Part Number' then [value] else '' end as [Customer Part Number],case when Description = 'Quote Value (Only For Product Support)' then [value] else '' end as [Quote Value (Only For Product Support)],case when Description = 'Date RFQ Status Set to Part Number Issues' then [value] else '' end as [Date RFQ Status Set to Part Number Issues],case when Description = 'Time RFQ Status Set to Part Number Issues' then [value] else '' end as [Time RFQ Status Set to Part Number Issues],case when Description = 'Date RFQ Status Set to Pending Customer Creation in SAP' then [value] else '' end as [Date RFQ Status Set to Pending Customer Creation in SAP],case when Description = 'Time RFQ Status Set to Pending Customer Creation in SAP' then [value] else '' end as [Time RFQ Status Set to Pending Customer Creation in SAP],case when Description = 'Date RFQ Status Set to Bid/No Bid Evaluation' then [value] else '' end as [Date RFQ Status Set to Bid/No Bid Evaluation],case when Description = 'Time RFQ Status Set to Bid/No Bid Evaluation' then [value] else '' end as [Time RFQ Status Set to Bid/No Bid Evaluation],case when Description = 'Date RFQ Status Set to QR Prep' then [value] else '' end as [Date RFQ Status Set to QR Prep],case when Description = 'Time RFQ Status Set to QR Prep' then [value] else '' end as [Time RFQ Status Set to QR Prep],case when Description = 'Date RFQ Status Set to Pricing' then [value] else '' end as [Date RFQ Status Set to Pricing],case when Description = 'Time RFQ Status Set to Pricing' then [value] else '' end as [Time RFQ Status Set to Pricing],case when Description = 'Pricing Approved by Product Support' then [value] else '' end as [Pricing Approved by Product Support],case when Description = 'Time RFQ Submitted to Production Control' then [value] else '' end as [Time RFQ Submitted to Production Control],case when Description = 'Time RFQ Submitted to Product Support' then [value] else '' end as [Time RFQ Submitted to Product Support],case when Description = 'Time of RFQ Product Support Decision' then [value] else '' end as [Time of RFQ Product Support Decision],case when Description = 'Date RFQ Status Set to Pre-Submittal' then [value] else '' end as [Date RFQ Status Set to Pre-Submittal],case when Description = 'Time RFQ Status Set to Pre-Submittal' then [value] else '' end as [Time RFQ Status Set to Pre-Submittal],case when Description = 'Time RFQ Submitted to Cust' then [value] else '' end as [Time RFQ Submitted to Cust],case when Description = 'Date of Disposition' then [value] else '' end as [Date of Disposition],case when Description = 'Time of Disposition' then [value] else '' end as [Time of Disposition],case when Description = 'Commercial/Military' then [value] else '' end as [Commercial/Military],case when Description = 'PO Number' then [value] else '' end as [PO Number],case when Description = 'Date of Complaint' then [value] else '' end as [Date of Complaint],case when Description = 'Complaint' then [value] else '' end as [Complaint],case when Description = 'Complaint Status' then [value] else '' end as [Complaint Status],case when Description = 'Complaint Assigned To' then [value] else '' end as [Complaint Assigned To],case when Description = 'Complainant Phone Number' then [value] else '' end as [Complainant Phone Number],case when Description = 'PO Rev Number' then [value] else '' end as [PO Rev Number],case when Description = 'PO Rev Date' then [value] else '' end as [PO Rev Date],case when Description = 'SO Updated' then [value] else '' end as [SO Updated],case when Description = 'Bid/No Bid Decision by Product Support' then [value] else '' end as [Bid/No Bid Decision by Product Support],case when Description = 'Complainant Email Address' then [value] else '' end as [Complainant Email Address],case when Description = 'Work Unit ID' then [value] else '' end as [Work Unit ID],case when Description = 'Assigned To' then [value] else '' end as [Assigned To],case when Description = 'Complainant Name' then [value] else '' end as [Complainant Name],case when Description = 'SO Creation Date' then [value] else '' end as [SO Creation Date],case when Description = 'RFQ Date Created' then [value] else '' end as [RFQ Date Created],case when Description = 'Customer' then [value] else '' end as [Customer],case when Description = 'Customer Contact' then [value] else '' end as [Customer Contact],case when Description = 'HRT Part Number' then [value] else '' end as [HRT Part Number],case when Description = 'Comments' then [value] else '' end as [Comments],case when Description = 'Status' then [value] else '' end as [Status],case when Description = 'RFQ Number' then [value] else '' end as [RFQ Number],case when Description = 'RFQ Work Unit ID' then [value] else '' end as [RFQ Work Unit ID],case when Description = 'OEM/After Market' then [value] else '' end as [OEM/After Market],case when Description = 'RFQ Date' then [value] else '' end as [RFQ Date],case when Description = 'Quote/QR Number' then [value] else '' end as [Quote/QR Number],case when Description = 'Owned By' then [value] else '' end as [Owned By],case when Description = 'Quote Expiration Date' then [value] else '' end as [Quote Expiration Date],case when Description = 'RFQ Customer Due Date' then [value] else '' end as [RFQ Customer Due Date],case when Description = 'Serial Number' then [value] else '' end as [Serial Number],case when Description = 'RFQ Time Created' then [value] else '' end as [RFQ Time Created],case when Description = 'Date Complaint Closed' then [value] else '' end as [Date Complaint Closed],case when Description = 'Time of Complaint' then [value] else '' end as [Time of Complaint],case when Description = 'Time Complaint Closed' then [value] else '' end as [Time Complaint Closed],case when Description = 'New Business?' then [value] else '' end as [New Business?]
Select table_name, row_no, max([SO Number]) as [SO Number],max([Date Created]) as [Date Created],max([Time Created]) as [Time Created],max([Date RFQ Submitted to Product Support]) as [Date RFQ Submitted to Product Support],max([Date RFQ Submitted to Production Control]) as [Date RFQ Submitted to Production Control],max([Date of RFQ Product Support Decision]) as [Date of RFQ Product Support Decision],max([Date RFQ Submitted to Cust]) as [Date RFQ Submitted to Cust],max([Gov Sol Number]) as [Gov Sol Number],max([RFQ Status]) as [RFQ Status],max([Program]) as [Program],max([Customer Part Number]) as [Customer Part Number],max([Quote Value (Only For Product Support)]) as [Quote Value (Only For Product Support)],max([Date RFQ Status Set to Part Number Issues]) as [Date RFQ Status Set to Part Number Issues],max([Time RFQ Status Set to Part Number Issues]) as [Time RFQ Status Set to Part Number Issues],max([Date RFQ Status Set to Pending Customer Creation in SAP]) as [Date RFQ Status Set to Pending Customer Creation in SAP],max([Time RFQ Status Set to Pending Customer Creation in SAP]) as [Time RFQ Status Set to Pending Customer Creation in SAP],max([Date RFQ Status Set to Bid/No Bid Evaluation]) as [Date RFQ Status Set to Bid/No Bid Evaluation],max([Time RFQ Status Set to Bid/No Bid Evaluation]) as [Time RFQ Status Set to Bid/No Bid Evaluation],max([Date RFQ Status Set to QR Prep]) as [Date RFQ Status Set to QR Prep],max([Time RFQ Status Set to QR Prep]) as [Time RFQ Status Set to QR Prep],max([Date RFQ Status Set to Pricing]) as [Date RFQ Status Set to Pricing],max([Time RFQ Status Set to Pricing]) as [Time RFQ Status Set to Pricing],max([Pricing Approved by Product Support]) as [Pricing Approved by Product Support],max([Time RFQ Submitted to Production Control]) as [Time RFQ Submitted to Production Control],max([Time RFQ Submitted to Product Support]) as [Time RFQ Submitted to Product Support],max([Time of RFQ Product Support Decision]) as [Time of RFQ Product Support Decision],max([Date RFQ Status Set to Pre-Submittal]) as [Date RFQ Status Set to Pre-Submittal],max([Time RFQ Status Set to Pre-Submittal]) as [Time RFQ Status Set to Pre-Submittal],max([Time RFQ Submitted to Cust]) as [Time RFQ Submitted to Cust],max([Date of Disposition]) as [Date of Disposition],max([Time of Disposition]) as [Time of Disposition],max([Commercial/Military]) as [Commercial/Military],max([PO Number]) as [PO Number],max([Date of Complaint]) as [Date of Complaint],max([Complaint]) as [Complaint],max([Complaint Status]) as [Complaint Status],max([Complaint Assigned To]) as [Complaint Assigned To],max([Complainant Phone Number]) as [Complainant Phone Number],max([PO Rev Number]) as [PO Rev Number],max([PO Rev Date]) as [PO Rev Date],max([SO Updated]) as [SO Updated],max([Bid/No Bid Decision by Product Support]) as [Bid/No Bid Decision by Product Support],max([Complainant Email Address]) as [Complainant Email Address],max([Work Unit ID]) as [Work Unit ID],max([Assigned To]) as [Assigned To],max([Complainant Name]) as [Complainant Name],max([SO Creation Date]) as [SO Creation Date],max([RFQ Date Created]) as [RFQ Date Created],max([Customer]) as [Customer],max([Customer Contact]) as [Customer Contact],max([HRT Part Number]) as [HRT Part Number],max([Comments]) as [Comments],max([Status]) as [Status],max([RFQ Number]) as [RFQ Number],max([RFQ Work Unit ID]) as [RFQ Work Unit ID],max([OEM/After Market]) as [OEM/After Market],max([RFQ Date]) as [RFQ Date],max([Quote/QR Number]) as [Quote/QR Number],max([Owned By]) as [Owned By],max([Quote Expiration Date]) as [Quote Expiration Date],max([RFQ Customer Due Date]) as [RFQ Customer Due Date],max([Serial Number]) as [Serial Number],max([RFQ Time Created]) as [RFQ Time Created],max([Date Complaint Closed]) as [Date Complaint Closed],max([Time of Complaint]) as [Time of Complaint],max([Time Complaint Closed]) as [Time Complaint Closed],max([New Business?]) as [New Business?]
from (
select Description as table_name, R.MasterKey as row_no, case when Description = 'SO Number' then [value] else '' end as [SO Number],case when Description = 'Date Created' then [value] else '' end as [Date Created],case when Description = 'Time Created' then [value] else '' end as [Time Created],case when Description = 'Date RFQ Submitted to Product Support' then [value] else '' end as [Date RFQ Submitted to Product Support],case when Description = 'Date RFQ Submitted to Production Control' then [value] else '' end as [Date RFQ Submitted to Production Control],case when Description = 'Date of RFQ Product Support Decision' then [value] else '' end as [Date of RFQ Product Support Decision],case when Description = 'Date RFQ Submitted to Cust' then [value] else '' end as [Date RFQ Submitted to Cust],case when Description = 'Gov Sol Number' then [value] else '' end as [Gov Sol Number],case when Description = 'RFQ Status' then [value] else '' end as [RFQ Status],case when Description = 'Program' then [value] else '' end as [Program],case when Description = 'Customer Part Number' then [value] else '' end as [Customer Part Number],case when Description = 'Quote Value (Only For Product Support)' then [value] else '' end as [Quote Value (Only For Product Support)],case when Description = 'Date RFQ Status Set to Part Number Issues' then [value] else '' end as [Date RFQ Status Set to Part Number Issues],case when Description = 'Time RFQ Status Set to Part Number Issues' then [value] else '' end as [Time RFQ Status Set to Part Number Issues],case when Description = 'Date RFQ Status Set to Pending Customer Creation in SAP' then [value] else '' end as [Date RFQ Status Set to Pending Customer Creation in SAP],case when Description = 'Time RFQ Status Set to Pending Customer Creation in SAP' then [value] else '' end as [Time RFQ Status Set to Pending Customer Creation in SAP],case when Description = 'Date RFQ Status Set to Bid/No Bid Evaluation' then [value] else '' end as [Date RFQ Status Set to Bid/No Bid Evaluation],case when Description = 'Time RFQ Status Set to Bid/No Bid Evaluation' then [value] else '' end as [Time RFQ Status Set to Bid/No Bid Evaluation],case when Description = 'Date RFQ Status Set to QR Prep' then [value] else '' end as [Date RFQ Status Set to QR Prep],case when Description = 'Time RFQ Status Set to QR Prep' then [value] else '' end as [Time RFQ Status Set to QR Prep],case when Description = 'Date RFQ Status Set to Pricing' then [value] else '' end as [Date RFQ Status Set to Pricing],case when Description = 'Time RFQ Status Set to Pricing' then [value] else '' end as [Time RFQ Status Set to Pricing],case when Description = 'Pricing Approved by Product Support' then [value] else '' end as [Pricing Approved by Product Support],case when Description = 'Time RFQ Submitted to Production Control' then [value] else '' end as [Time RFQ Submitted to Production Control],case when Description = 'Time RFQ Submitted to Product Support' then [value] else '' end as [Time RFQ Submitted to Product Support],case when Description = 'Time of RFQ Product Support Decision' then [value] else '' end as [Time of RFQ Product Support Decision],case when Description = 'Date RFQ Status Set to Pre-Submittal' then [value] else '' end as [Date RFQ Status Set to Pre-Submittal],case when Description = 'Time RFQ Status Set to Pre-Submittal' then [value] else '' end as [Time RFQ Status Set to Pre-Submittal],case when Description = 'Time RFQ Submitted to Cust' then [value] else '' end as [Time RFQ Submitted to Cust],case when Description = 'Date of Disposition' then [value] else '' end as [Date of Disposition],case when Description = 'Time of Disposition' then [value] else '' end as [Time of Disposition],case when Description = 'Commercial/Military' then [value] else '' end as [Commercial/Military],case when Description = 'PO Number' then [value] else '' end as [PO Number],case when Description = 'Date of Complaint' then [value] else '' end as [Date of Complaint],case when Description =

I appologize..

Here is the result from the print SQL

select Description as table_name, R.MasterKey as row_no, case when Description = 'SO Number' then [value] else '' end as [SO Number],case when Description = 'Date Created' then [value] else '' end as [Date Created],case when Description = 'Time Created' then [value] else '' end as [Time Created],case when Description = 'Date RFQ Submitted to Product Support' then [value] else '' end as [Date RFQ Submitted to Product Support],case when Description = 'Date RFQ Submitted to Production Control' then [value] else '' end as [Date RFQ Submitted to Production Control],case when Description = 'Date of RFQ Product Support Decision' then [value] else '' end as [Date of RFQ Product Support Decision],case when Description = 'Date RFQ Submitted to Cust' then [value] else '' end as [Date RFQ Submitted to Cust],case when Description = 'Gov Sol Number' then [value] else '' end as [Gov Sol Number],case when Description = 'RFQ Status' then [value] else '' end as [RFQ Status],case when Description = 'Program' then [value] else '' end as [Program],case when Description = 'Customer Part Number' then [value] else '' end as [Customer Part Number],case when Description = 'Quote Value (Only For Product Support)' then [value] else '' end as [Quote Value (Only For Product Support)],case when Description = 'Date RFQ Status Set to Part Number Issues' then [value] else '' end as [Date RFQ Status Set to Part Number Issues],case when Description = 'Time RFQ Status Set to Part Number Issues' then [value] else '' end as [Time RFQ Status Set to Part Number Issues],case when Description = 'Date RFQ Status Set to Pending Customer Creation in SAP' then [value] else '' end as [Date RFQ Status Set to Pending Customer Creation in SAP],case when Description = 'Time RFQ Status Set to Pending Customer Creation in SAP' then [value] else '' end as [Time RFQ Status Set to Pending Customer Creation in SAP],case when Description = 'Date RFQ Status Set to Bid/No Bid Evaluation' then [value] else '' end as [Date RFQ Status Set to Bid/No Bid Evaluation],case when Description = 'Time RFQ Status Set to Bid/No Bid Evaluation' then [value] else '' end as [Time RFQ Status Set to Bid/No Bid Evaluation],case when Description = 'Date RFQ Status Set to QR Prep' then [value] else '' end as [Date RFQ Status Set to QR Prep],case when Description = 'Time RFQ Status Set to QR Prep' then [value] else '' end as [Time RFQ Status Set to QR Prep],case when Description = 'Date RFQ Status Set to Pricing' then [value] else '' end as [Date RFQ Status Set to Pricing],case when Description = 'Time RFQ Status Set to Pricing' then [value] else '' end as [Time RFQ Status Set to Pricing],case when Description = 'Pricing Approved by Product Support' then [value] else '' end as [Pricing Approved by Product Support],case when Description = 'Time RFQ Submitted to Production Control' then [value] else '' end as [Time RFQ Submitted to Production Control],case when Description = 'Time RFQ Submitted to Product Support' then [value] else '' end as [Time RFQ Submitted to Product Support],case when Description = 'Time of RFQ Product Support Decision' then [value] else '' end as [Time of RFQ Product Support Decision],case when Description = 'Date RFQ Status Set to Pre-Submittal' then [value] else '' end as [Date RFQ Status Set to Pre-Submittal],case when Description = 'Time RFQ Status Set to Pre-Submittal' then [value] else '' end as [Time RFQ Status Set to Pre-Submittal],case when Description = 'Time RFQ Submitted to Cust' then [value] else '' end as [Time RFQ Submitted to Cust],case when Description = 'Date of Disposition' then [value] else '' end as [Date of Disposition],case when Description = 'Time of Disposition' then [value] else '' end as [Time of Disposition],case when Description = 'Commercial/Military' then [value] else '' end as [Commercial/Military],case when Description = 'PO Number' then [value] else '' end as [PO Number],case when Description = 'Date of Complaint' then [value] else '' end as [Date of Complaint],case when Description =

mark_wills:

I was running some test on your original solution (using your table structures and everything).

The solution works fine, however, I created a program that would generate dummy records.

I created 100,000 records of template type contacts... when i ran the stored procedure it took around 8-10 seconds.

I created 100,000 more so 200,000 total and the procedure is taking between 2-3 minutes.  Of course this execute speed is unacceptable.  Is there anyway to speed this up?  Is this expected?

Thanks

Well, it is simply a case of "too big" to fit everything inside the 8000 byte variable.

We can get rid of syntactically unecessary characters, but still wont be enough (but will help), but what would really help is if we can use something other the name for first gathering - we can still express it as the name, but if every column / field name has a unique identifier, then we can use that...

If not, then we can still do something like it, just create a temp table of column / field names with an identity column, but if there is already a unique field identifier other than it's name then it would be better...

So, if you can supply the actual table columns, or simply, the unique identifier for each field can make it "fit" within 8000.

For example, have a look at using that temp table idea :

declare @templateid varchar(250)
set @templateid = 'put a value here'
 
select identity(int,1,1) as ID, description into #tmp_column_list from ieuser.DataTypes where SystemCode = @templateid 
 
declare @c varchar(8000)
select @c = isnull(@c + ',','') + 'max([' + convert(varchar,id) + ']) [' + Description + ']' from #tmp_column_list
print @c
 
declare @s varchar(8000)
select @s = isnull(@S + ',','') + 'case when Description = '''+ Description + ''' then value else '''' end [' + convert(varchar,id) + ']' from #tmp_column_list
print @s
 
declare @r varchar(80)
set @r = convert(varchar,@templateid)
declare @sql varchar(8000)
 
set @sql = 'Select table_name, row_no, '+@c+'
from (
select Description as table_name, R.MasterKey as row_no, '+@s+'
from ieuser.data D
inner join ieuser.systemcodes T on T.SystemCode = D.SystemCode
inner join ieuser.masterkey R on R.masterkey = D.masterkey
inner join ieuser.DataTypes F on F.SystemCode = D.SystemCode and F.dataid = D.dataid
where T.SystemCode = '+@r + ') s
Group by table_name,Row_no'
 
print @sql
 
exec (@sql)
 
drop table #tmp_column_list
 
GO
 
-- and as a test, using the original code sample on those ee_ tables above :
 
declare @templateid int
set @templateid = 1
 
select identity(int,1,1) as ID, FieldDisplayName into #tmp_column_list from ee_fields where templateid = @templateid
 
declare @c varchar(8000)
select @c = isnull(@c + ',','') + 'max([' + convert(varchar,id) + ']) [' + FieldDisplayName + ']' from #tmp_column_list
print @c
 
declare @s varchar(8000)
select @s = isnull(@S + ',','') + 'case when FieldDisplayName = '''+ FieldDisplayName + ''' then fieldvalue else '''' end [' + convert(varchar,id) + ']' from #tmp_column_list
print @s
 
declare @r varchar(80)
set @r = convert(varchar,@templateid)
 
exec ('Select table_name, row_no, '+@c+'
from (
select TemplateDisplayName as table_name, R.Recordid as row_no, '+@s+'
from ee_datavalues D
inner join ee_template T on T.templateid = D.templateid
inner join ee_records R on R.recordid = D.recordid
inner join ee_fields F on F.templateid = D.templateid and F.fieldid = D.fieldid
where T.templateid = '+@r + ') s
Group by table_name,Row_no')
 
drop table #tmp_column_list

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:

Oh, in terms of speed, then you will need to make sure of a few indexes...

ieuser.systemcodes T on T.SystemCode

ieuser.masterkey R on R.masterkey

ieuser.DataTypes F on F.SystemCode and F.dataid

ieuser.data D on D.systemCode

and then in the "where" clause also change it from T.systemcode to D.systemcode ie:

from :  where T.SystemCode = '+@r + ') s
to :  where D.SystemCode = '+@r + ') s

That way it should filter the ieuser.data table first.

P.S. the example EE_ tables do not have any indexes on the appropriate columns and so, yes it would be expected to run slow due to entire table scans rather than index seeks.

if you want, would suggest (without thinking about it too much) the following indexes on those test tables :

create index idx_EE_fields_Template on EE_Fields (templateid)    
-- no need to include fieldid above because it is the clustered primary key and secondary indexes will use that PK as a row pointer.
create index idx_EE_datavalues_Template on EE_DataValues (templateid,recordid,FieldID)

and also do the same change :
from :  where T.SystemCode = '+@r + ') s
to :  where D.SystemCode = '+@r + ') s

If needed, we can change the sequence of joins around a bit so that the "driving" table (ie the first in the FROM) has least rows, and joins via indexes on subsequent tables in sequence of data needed. But try the indexes first and changing that "where" clause...

When i run this

declare @templateid varchar(250)
set @templateid = 'ISSUES'
 
select identity(int,1,1) as ID, description into #tmp_column_list from ieuser.DataTypes where SystemCode = @templateid
 
declare @c varchar(8000)
select @c = isnull(@c + ',','') + 'max([' + convert(varchar,id) + ']) [' + Description + ']' from #tmp_column_list
print @c
 
declare @s varchar(8000)
select @s = isnull(@S + ',','') + 'case when Description = '''+ Description + ''' then value else '''' end [' + convert(varchar,id) + ']' from #tmp_column_list
print @s
 
declare @r varchar(80)
set @r = convert(varchar,@templateid)
declare @sql varchar(8000)
 
set @sql = 'Select table_name, row_no, '+@c+'
from (
select Description as table_name, R.MasterKey as row_no, '+@s+'
from ieuser.data D
inner join ieuser.systemcodes T on T.SystemCode = D.SystemCode
inner join ieuser.masterkey R on R.masterkey = D.masterkey
inner join ieuser.DataTypes F on F.SystemCode = D.SystemCode and F.dataid = D.dataid
where T.SystemCode = '+@r + ') s
Group by table_name,Row_no'
 
print @sql
 
exec (@sql)
 
drop table #tmp_column_list
 
GO

I recieve

(13 row(s) affected)

max([1]) [Comments],max([2]) [Assigned To],max([3]) [Date of Disposition],max([4]) [Time of Disposition],max([5]) [Date Opened],max([6]) [Time Opened],max([7]) [Work Unit ID],max([8]) [Date Created],max([9]) [Submitted By],max([10]) [Status],max([11]) [Description],max([12]) [Type],max([13]) [Time Created]
case when Description = 'Comments' then value else '' end [1],case when Description = 'Assigned To' then value else '' end [2],case when Description = 'Date of Disposition' then value else '' end [3],case when Description = 'Time of Disposition' then value else '' end [4],case when Description = 'Date Opened' then value else '' end [5],case when Description = 'Time Opened' then value else '' end [6],case when Description = 'Work Unit ID' then value else '' end [7],case when Description = 'Date Created' then value else '' end [8],case when Description = 'Submitted By' then value else '' end [9],case when Description = 'Status' then value else '' end [10],case when Description = 'Description' then value else '' end [11],case when Description = 'Type' then value else '' end [12],case when Description = 'Time Created' then value else '' end [13]
Select table_name, row_no, max([1]) [Comments],max([2]) [Assigned To],max([3]) [Date of Disposition],max([4]) [Time of Disposition],max([5]) [Date Opened],max([6]) [Time Opened],max([7]) [Work Unit ID],max([8]) [Date Created],max([9]) [Submitted By],max([10]) [Status],max([11]) [Description],max([12]) [Type],max([13]) [Time Created]
from (
select Description as table_name, R.MasterKey as row_no, case when Description = 'Comments' then value else '' end [1],case when Description = 'Assigned To' then value else '' end [2],case when Description = 'Date of Disposition' then value else '' end [3],case when Description = 'Time of Disposition' then value else '' end [4],case when Description = 'Date Opened' then value else '' end [5],case when Description = 'Time Opened' then value else '' end [6],case when Description = 'Work Unit ID' then value else '' end [7],case when Description = 'Date Created' then value else '' end [8],case when Description = 'Submitted By' then value else '' end [9],case when Description = 'Status' then value else '' end [10],case when Description = 'Description' then value else '' end [11],case when Description = 'Type' then value else '' end [12],case when Description = 'Time Created' then value else '' end [13]
from ieuser.data D
inner join ieuser.systemcodes T on T.SystemCode = D.SystemCode
inner join ieuser.masterkey R on R.masterkey = D.masterkey
inner join ieuser.DataTypes F on F.SystemCode = D.SystemCode and F.dataid = D.dataid
where T.SystemCode = ISSUES) s
Group by table_name,Row_no
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ISSUES'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.

Well, that is getting a lot better actually. It means it is running the query. But there is more than one column known as "description" and so we need to prefix it with the correct alias.

that is all done in setting the @s variable. Think it should be :

declare @s varchar(8000)
select @s = isnull(@S + ',','') + 'case when F.Description = '''+ Description + ''' then value else '''' end [' + convert(varchar,id) + ']' from #tmp_column_list
print @s

declare @templateid varchar(250)
set @templateid = 'ISSUES'
 
select identity(int,1,1) as ID, description into #tmp_column_list from ieuser.DataTypes where SystemCode = @templateid
 
declare @c varchar(8000)
select @c = isnull(@c + ',','') + 'max([' + convert(varchar,id) + ']) [' + Description + ']' from #tmp_column_list
print @c
 
declare @s varchar(8000)
select @s = isnull(@S + ',','') + 'case when F.Description = '''+ Description + ''' then value else '''' end [' + convert(varchar,id) + ']' from #tmp_column_list
print @s
 
declare @r varchar(80)
set @r = convert(varchar,@templateid)
declare @sql varchar(8000)
 
set @sql = 'Select table_name, row_no, '+@c+'
from (
select Description as table_name, R.MasterKey as row_no, '+@s+'
from ieuser.data D
inner join ieuser.systemcodes T on T.SystemCode = D.SystemCode
inner join ieuser.masterkey R on R.masterkey = D.masterkey
inner join ieuser.DataTypes F on F.SystemCode = D.SystemCode and F.dataid = D.dataid
where T.SystemCode = '+@r + ') s
Group by table_name,Row_no'
 
print @sql
 
exec (@sql)
 
drop table #tmp_column_list
 
GO

Now produces
max([1]) [Comments],max([2]) [Assigned To],max([3]) [Date of Disposition],max([4]) [Time of Disposition],max([5]) [Date Opened],max([6]) [Time Opened],max([7]) [Work Unit ID],max([8]) [Date Created],max([9]) [Submitted By],max([10]) [Status],max([11]) [Description],max([12]) [Type],max([13]) [Time Created]
case when F.Description = 'Comments' then value else '' end [1],case when F.Description = 'Assigned To' then value else '' end [2],case when F.Description = 'Date of Disposition' then value else '' end [3],case when F.Description = 'Time of Disposition' then value else '' end [4],case when F.Description = 'Date Opened' then value else '' end [5],case when F.Description = 'Time Opened' then value else '' end [6],case when F.Description = 'Work Unit ID' then value else '' end [7],case when F.Description = 'Date Created' then value else '' end [8],case when F.Description = 'Submitted By' then value else '' end [9],case when F.Description = 'Status' then value else '' end [10],case when F.Description = 'Description' then value else '' end [11],case when F.Description = 'Type' then value else '' end [12],case when F.Description = 'Time Created' then value else '' end [13]
Select table_name, row_no, max([1]) [Comments],max([2]) [Assigned To],max([3]) [Date of Disposition],max([4]) [Time of Disposition],max([5]) [Date Opened],max([6]) [Time Opened],max([7]) [Work Unit ID],max([8]) [Date Created],max([9]) [Submitted By],max([10]) [Status],max([11]) [Description],max([12]) [Type],max([13]) [Time Created]
from (
select Description as table_name, R.MasterKey as row_no, case when F.Description = 'Comments' then value else '' end [1],case when F.Description = 'Assigned To' then value else '' end [2],case when F.Description = 'Date of Disposition' then value else '' end [3],case when F.Description = 'Time of Disposition' then value else '' end [4],case when F.Description = 'Date Opened' then value else '' end [5],case when F.Description = 'Time Opened' then value else '' end [6],case when F.Description = 'Work Unit ID' then value else '' end [7],case when F.Description = 'Date Created' then value else '' end [8],case when F.Description = 'Submitted By' then value else '' end [9],case when F.Description = 'Status' then value else '' end [10],case when F.Description = 'Description' then value else '' end [11],case when F.Description = 'Type' then value else '' end [12],case when F.Description = 'Time Created' then value else '' end [13]
from ieuser.data D
inner join ieuser.systemcodes T on T.SystemCode = D.SystemCode
inner join ieuser.masterkey R on R.masterkey = D.masterkey
inner join ieuser.DataTypes F on F.SystemCode = D.SystemCode and F.dataid = D.dataid
where T.SystemCode = ISSUES) s
Group by table_name,Row_no
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ISSUES'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Description'.

Let me also state that in the data values table, it's not garunteed that there is field value entry for every field for the particular template.

I'm not sure if the query tests for that or not but I thought i'd let you know.

Any ideas?

Thanks.

Might need to change sequence and to an outer join if you want the column title without an entry or with a 'blank' entry in there, otherwise it is just showing those actually in use...

OK, I solved one of the errors.

Here is the updated code and results

declare @templateid varchar(250)
set @templateid = 'ISSUES'
 
select identity(int,1,1) as ID, description into #tmp_column_list from ieuser.DataTypes where SystemCode = @templateid
 
declare @c varchar(8000)
select @c = isnull(@c + ',','') + 'max([' + convert(varchar,id) + ']) [' + Description + ']' from #tmp_column_list
print @c
 
declare @s varchar(8000)
select @s = isnull(@S + ',','') + 'case when F.Description = '''+ Description + ''' then value else '''' end [' + convert(varchar,id) + ']' from #tmp_column_list
print @s
 
declare @r varchar(80)
set @r = convert(varchar,@templateid)
declare @sql varchar(8000)
 
set @sql = 'Select table_name, row_no, '+@c+'
from (
select T.Description as table_name, R.MasterKey as row_no, '+@s+'
from ieuser.data D
inner join ieuser.systemcodes T on T.SystemCode = D.SystemCode
inner join ieuser.masterkey R on R.masterkey = D.masterkey
inner join ieuser.DataTypes F on F.SystemCode = D.SystemCode and F.dataid = D.dataid
where T.SystemCode = '+@r + ') s
Group by table_name,Row_no'
 
print @sql
 
exec (@sql)
 
drop table #tmp_column_list
 
GO


results


(13 row(s) affected)

max([1]) [Comments],max([2]) [Assigned To],max([3]) [Date of Disposition],max([4]) [Time of Disposition],max([5]) [Date Opened],max([6]) [Time Opened],max([7]) [Work Unit ID],max([8]) [Date Created],max([9]) [Submitted By],max([10]) [Status],max([11]) [Description],max([12]) [Type],max([13]) [Time Created]
case when F.Description = 'Comments' then value else '' end [1],case when F.Description = 'Assigned To' then value else '' end [2],case when F.Description = 'Date of Disposition' then value else '' end [3],case when F.Description = 'Time of Disposition' then value else '' end [4],case when F.Description = 'Date Opened' then value else '' end [5],case when F.Description = 'Time Opened' then value else '' end [6],case when F.Description = 'Work Unit ID' then value else '' end [7],case when F.Description = 'Date Created' then value else '' end [8],case when F.Description = 'Submitted By' then value else '' end [9],case when F.Description = 'Status' then value else '' end [10],case when F.Description = 'Description' then value else '' end [11],case when F.Description = 'Type' then value else '' end [12],case when F.Description = 'Time Created' then value else '' end [13]
Select table_name, row_no, max([1]) [Comments],max([2]) [Assigned To],max([3]) [Date of Disposition],max([4]) [Time of Disposition],max([5]) [Date Opened],max([6]) [Time Opened],max([7]) [Work Unit ID],max([8]) [Date Created],max([9]) [Submitted By],max([10]) [Status],max([11]) [Description],max([12]) [Type],max([13]) [Time Created]
from (
select T.Description as table_name, R.MasterKey as row_no, case when F.Description = 'Comments' then value else '' end [1],case when F.Description = 'Assigned To' then value else '' end [2],case when F.Description = 'Date of Disposition' then value else '' end [3],case when F.Description = 'Time of Disposition' then value else '' end [4],case when F.Description = 'Date Opened' then value else '' end [5],case when F.Description = 'Time Opened' then value else '' end [6],case when F.Description = 'Work Unit ID' then value else '' end [7],case when F.Description = 'Date Created' then value else '' end [8],case when F.Description = 'Submitted By' then value else '' end [9],case when F.Description = 'Status' then value else '' end [10],case when F.Description = 'Description' then value else '' end [11],case when F.Description = 'Type' then value else '' end [12],case when F.Description = 'Time Created' then value else '' end [13]
from ieuser.data D
inner join ieuser.systemcodes T on T.SystemCode = D.SystemCode
inner join ieuser.masterkey R on R.masterkey = D.masterkey
inner join ieuser.DataTypes F on F.SystemCode = D.SystemCode and F.dataid = D.dataid
where T.SystemCode = ISSUES) s
Group by table_name,Row_no
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ISSUES'.

Might need to change sequence and to an outer join if you want the column title without an entry or with a 'blank' entry in there, otherwise it is just showing those actually in use...

Can you elaborate on this?  I have such a tough time with SQL.. the language isn't very logical to me.

My question was.. since its not garunteed that  record has a value entry for EVERY field, is it possible that this is causing the error?

Once we can get this working, it would be super cool if you could add comments to the store precedure explaining to me what is going on.

If i can find a way to up the points to the question I will.  You will most def be recieiving the points for this question, I'm just trying to get a working solution -- and help understanding why it works.

Thanks a lot for all of your help.

Right, with "ISSUES" we are not encapsulating the @r (my original example had the id as an integer), and the description alias, there is one more instance, but this time, it is the actual template name...


declare @templateid varchar(250)
set @templateid = 'ISSUES'
 
select identity(int,1,1) as ID, description into #tmp_column_list from ieuser.DataTypes where SystemCode = @templateid
 
declare @c varchar(8000)
select @c = isnull(@c + ',','') + 'max([' + convert(varchar,id) + ']) [' + Description + ']' from #tmp_column_list
print @c
 
declare @s varchar(8000)
select @s = isnull(@S + ',','') + 'case when F.Description = '''+ Description + ''' then value else '''' end [' + convert(varchar,id) + ']' from #tmp_column_list
print @s
 
declare @r varchar(80)
set @r = convert(varchar,@templateid)
declare @sql varchar(8000)
 
set @sql = 'Select table_name, row_no, '+@c+'
from (
select T.Description as table_name, R.MasterKey as row_no, '+@s+'
from ieuser.data D
inner join ieuser.systemcodes T on T.SystemCode = D.SystemCode
inner join ieuser.masterkey R on R.masterkey = D.masterkey
inner join ieuser.DataTypes F on F.SystemCode = D.SystemCode and F.dataid = D.dataid
where T.systemcode = '''+@r + ''') s
Group by table_name,Row_no'
 
print @sql
 
exec (@sql)
 
drop table #tmp_column_list
 

OK.. That script worked for 'issues'... however, 'contfile' has many more columns and it appears that we are exceeding the 8000 limit again.

I don't mind if the column names are the DataID from the DataTypes table rather than the actual column name.  Would this fix it?  Can you help me out with an example?

Thanks.

Is it possible to get the datatypes table as an Excel Spreadsheet ?

>> My question was.. since its not garunteed that  record has a value entry for EVERY field, is it possible that this is causing the error?
would not be causing the error as I think we have now discovered...

>> Once we can get this working, it would be super cool if you could add comments to the store precedure explaining to me what is going on.
no problems...

>> If i can find a way to up the points to the question I will.
not needed...

>> Thanks a lot for all of your help
now we are talking :)

Ok.. Attached is a zip file that contains the SQL to generate my DB tables.

PLEASE note... the coumn nmaes aren't very good.. it snot old database.. when I asked the questions I changed them so people could understand better.

So what I need is to show the user all of the records for a particular template (systemcode) AND folder (folderID).

Thanks for all of you rhelp.

OK, After experimenting with indexes, I've finally achieved performance that I can deal with for now.  The only problem with the query listed below is that I get errors if the template has too many columns.

As I've stated before, the DataID will work just fine for the column name.  Any ideas on how to change this query to prevent large templates from exceeding the 8000 limit?

Thanks -- I'm so close..

declare @templateid varchar(250)
set @templateid = 'TESTDATA'
 
select identity(int,1,1) as ID, description into #tmp_column_list from ieuser.DataTypes where SystemCode = @templateid
 
declare @c varchar(8000)
select @c = isnull(@c + ',','') + 'max([' + convert(varchar,id) + ']) [' + Description + ']' from #tmp_column_list
print @c
 
declare @s varchar(8000)
select @s = isnull(@S + ',','') + 'case when F.Description = '''+ Description + ''' then value else '''' end [' + convert(varchar,id) + ']' from #tmp_column_list
print @s
 
declare @r varchar(80)
set @r = convert(varchar,@templateid)
declare @sql varchar(8000)
 
set @sql = 'Select table_name, row_no, '+@c+'
from (
select T.Description as table_name, R.MasterKey as row_no, '+@s+'
from ieuser.data D
inner join ieuser.systemcodes T on T.SystemCode = D.SystemCode
inner join ieuser.masterkey R on R.masterkey = D.masterkey
inner join ieuser.DataTypes F on F.SystemCode = D.SystemCode and F.dataid = D.dataid
where T.systemcode = '''+@r + ''') s
Group by table_name,Row_no'
 
print @sql
 
exec (@sql)
 
drop table #tmp_column_list

Perfect!!!

Are there any drawbacks I should be aware of with this approach?  Like how many fields should I limit per template to prevent exceeding the limit again?

Also, Could you please post the solution above with Line by line comments so I can understand what is going on?  I can write code in many languages but SQL has never made sense to me, especially using it in this way.  Thank you so much for your help.

Well, the only one is really "size". You might need to somehow flag the "important" columns if it gets too big.

SQL2000 has a varchar limit of 8000 bytes. That changes in SQL2005 and SQL2008 but you would still be limited by the exec command and would need to move to the executesql and pass in parameters of the column selections.

So, let's deal with 8000 bytes (below) suggests about 110 columns. If you play with the working below, you can see for yourself.

Scroll down below, and you will see an alternate method without using a subquery, and that does save a bit of space and could yield 134 columns.

Any more columns and we would have to resort to populating a temp table and then select from that...

/*
Breaking down the various elements:
 
Select table_name, row_no,                                 = 26 bytes
 
from (                                                     = 6 bytes
 
select T.Description as table_name, R.MasterKey as row_no, = 58 bytes
 
max([001]) [001],                                          = 17 bytes per column (allow 3 digits)
 
case when f.dataid = 001 then value else '' end [001],     = 54 bytes per column
 
 
from ieuser.data D (and the rest)                          = 290 bytes
 
 
So, fixed elements = 290 + 58 + 6 + 26 = 380 (say 400) from 8000 = 7600 / 71 bytes per column = 107 columns, even at 65 bytes per column (avg 2 digit) = 117 columns
*/
 
--Now, in having a good close look, we do not need a subquery and can make up a bit more via an alternate method :
 
 
declare @templateid varchar(250)
set @templateid = 'TESTDATA'
 
declare @s varchar(8000)
select @s = isnull(@S + ',','') + 'max(case when F.DataID=' + convert(varchar,dataid) + ' then value else '''' end) [' + convert(varchar,dataid) + ']' from ieuser.DataTypes where SystemCode = @templateid 
print @s
 
declare @r varchar(80)
set @r = convert(varchar,@templateid)
 
declare @sql varchar(8000)
 
set @sql = 'select T.Description, R.MasterKey, '+@s+'
from ieuser.data D
inner join ieuser.systemcodes T on T.SystemCode = D.SystemCode
inner join ieuser.masterkey R on R.masterkey = D.masterkey
inner join ieuser.DataTypes F on F.SystemCode = D.SystemCode and F.dataid = D.dataid
where T.systemcode = '''+@r + '''
Group by T.Description, R.MasterKey'
 
print @sql
 
exec (@sql)
 
-- so lets now look at those components :
/*
select T.Description, R.MasterKey,                         = 34 bytes
 
max(case when F.dataid=001 then value else '' end) [001],  = 57 bytes per column
 
from ieuser.data D (and the rest)                          = 290 bytes
 
 
alternate method : 8000 - 324 = 7676 / 57 = 134 columns
*/

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:

Thanks so much.  Now.. Here is my final solution.  Could you please add some comments to help me to learn what is going on.  I know how to do selects and what not but I'm completley confused on why or how this solution works.  Here is my final solution.

declare @templateid varchar(250)
set @templateid = 'CONTFILE'
 
select dataid as ID, description into #tmp_column_list from ieuser.DataTypes where SystemCode = @templateid 
 
declare @c varchar(8000)
select @c = isnull(@c + ',','') + 'max([' + convert(varchar,id) + ']) [' + convert(varchar,id) + ']' from #tmp_column_list
print @c
 
declare @s varchar(8000)
select @s = isnull(@S + ',','') + 'case when F.DataID = ' + convert(varchar,id) + ' then value else '''' end [' + convert(varchar,id) + ']' from #tmp_column_list
print @s
 
declare @r varchar(80)
set @r = convert(varchar,@templateid)
declare @sql varchar(8000)
 
set @sql = 'Select table_name, row_no, '+@c+'
from (
select T.Description as table_name, R.MasterKey as row_no, R.FolderID, '+@s+'
from ieuser.data D
inner join ieuser.systemcodes T on T.SystemCode = D.SystemCode
inner join ieuser.masterkey R on R.masterkey = D.masterkey
inner join ieuser.DataTypes F on F.SystemCode = D.SystemCode and F.dataid = D.dataid
where T.systemcode = '''+@r + ''' AND R.FolderID = 173) s
Group by table_name,Row_no'
 
print @sql
 
exec (@sql)
 
drop table #tmp_column_list
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:

Excellent Expert.. Thanks so much.