December 13, 2007 at 10:44 pm
I have data in a relatively normalized structure, that I want to un-normalize into a Horizontal (single-row-per-person) format to make it easier for users to query on. (data/queries abbreviated for the sake of efficiency and not boring the heckout of anyone reading):
The metadata table:
attribute_id description import_key select_mask sort_order
-11 Not applying noessay -1 -14
-8 P essay received Precd -1 -8
-7 P essay q sent Psent -1 -7
-6 P Invite Pinvite -1 -6
-5 P Deny Pdeny -1 -5
-4 P Recipient Precipient -1 -4
-3 P Offer Poffer -1 -3
-2 P Waitlist Pwaitlist -1 -2
-1 P Hold Phold -1 -1
The applicant Data table
applicant_user_id attribute_id
182182 -11
182387 -11
182388 -8
182388 -7
182463 -10
182463 -8
182463 -7
182645 -12
182645 -8
182645 -7
Desired output
applicant_user_id Not applying P essay received P essay q sent P Invite P Deny P Recipient P Offer P Waitlist P Hold
182182 1 0 0 0 0 0 0 0 0
182387 1 0 0 0 0 0 0 0 0
182388 0 1 0 0 0 0 0 0 0
182388 0 0 1 0 0 0 0 0 0
182463 0 0 0 0 0 0 0 0 0
182463 0 1 0 0 0 0 0 0 0
182463 0 0 1 0 0 0 0 0 0
182645 0 0 0 0 0 0 0 0 0
182645 0 1 0 0 0 0 0 0 0
182645 0 0 1 0 0 0 0 0 0
I can use this query style (abbreviated for sake efficiency):
Select applicant_user_id,
'Not applying' =
CASE
WHEN [Attribute_id]='-11' THEN 1
ELSE 0
END,
'P essay received' =
CASE
WHEN [Attribute_id]='-8' THEN 1
ELSE 0
END,
....
FROM Applicant_Attributes
WHERE applicant_user_id in ('182182','182387','182388','182388','182463','182463','182463','182645','182645','182645')
But, ideally, I'd like an automated way to do this. (select * from General_Attributes,
then loop over case statement with variables to build the query string). I'd like to do this so when the values change in the table, I don't have to rewrite the query each time.
I tinkered with a couple sql ideas, but before I got to far, it occured to me the challenge would be getting the built query string to parse (ie when using CF or ASP, the code builds the string, then the string is evaluated in the sql statement).
So… any ideas or resources? Is it even feasible in just T-SQL?
TIA,
Chris
P.S. apologies if this in any way appears like I am talking down to people. This is my first post on this forum, and having not spent time researching other posts, I don't know what style people prefer, so I went with the lowest common denominator...
December 14, 2007 at 5:11 am
Hi Chris,
I've taken a quick look at this and may have something of use.
First off I have a couple of scripts to create the tables and data, presumably you won't need these.
-------------------------------------------------------------------------
create table metadata
(attribute_id int not null, [description] varchar(20), import_key varchar(20), select_mask int, sort_order int)
insert into metadata
(attribute_id, [description], import_key, select_mask, sort_order)
values
(-11, 'Not applying', 'noessay', -1, -14)
insert into metadata
(attribute_id, [description], import_key, select_mask, sort_order)
values
(-8, 'P essay received', 'Precd', -1, -8)
insert into metadata
(attribute_id, [description], import_key, select_mask, sort_order)
values
(-7, 'P essay q sent', 'Psent', -1, -7)
insert into metadata
(attribute_id, [description], import_key, select_mask, sort_order)
values
(-6, 'P Invite', 'Pinvite', -1, -6)
insert into metadata
(attribute_id, [description], import_key, select_mask, sort_order)
values
(-5, 'P Deny', 'Pdeny', -1, -5)
insert into metadata
(attribute_id, [description], import_key, select_mask, sort_order)
values
(-4, 'P Recipient', 'Precipient', -1, -4)
insert into metadata
(attribute_id, [description], import_key, select_mask, sort_order)
values
(-3, 'P Offer', 'Poffer', -1, -3)
insert into metadata
(attribute_id, [description], import_key, select_mask, sort_order)
values
(-2, 'P Waitlist', 'Pwaitlist', -1, -2)
insert into metadata
(attribute_id, [description], import_key, select_mask, sort_order)
values
(-1, 'P Hold', 'Phold', -1, -1)
create table applicantdata
(applicant_user_id int not null, attribute_id int not null)
insert into applicantdata
(applicant_user_id, attribute_id)
values
(182182, -11)
insert into applicantdata
(applicant_user_id, attribute_id)
values
(182387, -11)
insert into applicantdata
(applicant_user_id, attribute_id)
values
(182388, -8)
insert into applicantdata
(applicant_user_id, attribute_id)
values
(182388, -7)
insert into applicantdata
(applicant_user_id, attribute_id)
values
(182463, -10)
insert into applicantdata
(applicant_user_id, attribute_id)
values
(182463, -8)
insert into applicantdata
(applicant_user_id, attribute_id)
values
(182463, -7)
insert into applicantdata
(applicant_user_id, attribute_id)
values
(182645, -12)
insert into applicantdata
(applicant_user_id, attribute_id)
values
(182645, -8)
insert into applicantdata
(applicant_user_id, attribute_id)
values
(182645, -7)
-------------------------------------------------------------------------
Now the actual query - substitute you own table names in here
-------------------------------------------------------------------------
declare @strSQL varchar(1000)
declare @currentattribute_id int
declare @currentdescription varchar(20)
select @strSQL = 'select applicant_user_id'
declare metadatacursor cursor for
select attribute_id, [description]
from metadata
order by sort_order
open metadatacursor
fetch next from metadatacursor into @currentattribute_id, @currentdescription
while @@FETCH_STATUS = 0
begin
select @strSQL = @strSQL + ', ''' + @currentdescription + ''' = CASE WHEN [Attribute_id]= ' + cast(@currentattribute_id as nvarchar(4)) + ' THEN 1 ELSE 0 END'
fetch next from metadatacursor into @currentattribute_id, @currentdescription
end
close metadatacursor
deallocate metadatacursor
select @strSQL = @strSQL + ' FROM Applicantdata'
exec (@strSQL)
-------------------------------------------------------------------------
This produces exactly the same output that you specified, though I noticed that you have multiple lines for the same applicant_user_id so I haven't attempted to group these together.
Hope this is helpful.
Marcus
December 14, 2007 at 7:02 am
First, please take a look at the following to see how to get better answers quicker...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
With that in mind, here's the test tables and data I used... (scroll bars are way over to the right if you want to look at the whole thing on screen)...
--===== Create two test tables... this is NOT part of the solution
CREATE TABLE #Attribute
(
Attribute_ID SMALLINT,
Description VARCHAR(30),
Import_Key VARCHAR(20),
Select_Mask SMALLINT,
Sort_Order SMALLINT
)
CREATE TABLE #Applicant_User
(
Applicant_User_ID INT,
Attribute_ID SMALLINT
)
--===== Populate the two test tables... this is NOT part of the solution
INSERT INTO #Attribute
(Attribute_ID, Description, Import_Key, Select_Mask, Sort_Order)
SELECT -11,'Not applying' ,'noessay' ,-1,-14 UNION ALL
SELECT -8 ,'P essay received','Precd' ,-1,-8 UNION ALL
SELECT -7 ,'P essay q sent' ,'Psent' ,-1,-7 UNION ALL
SELECT -6 ,'P Invite' ,'Pinvite' ,-1,-6 UNION ALL
SELECT -5 ,'P Deny' ,'Pdeny' ,-1,-5 UNION ALL
SELECT -4 ,'P Recipient' ,'Precipient',-1,-4 UNION ALL
SELECT -3 ,'P Offer' ,'Poffer' ,-1,-3 UNION ALL
SELECT -2 ,'P Waitlist' ,'Pwaitlist' ,-1,-2 UNION ALL
SELECT -1 ,'P Hold' ,'Phold' ,-1,-1
INSERT INTO #Applicant_User
(Applicant_User_ID, Attribute_ID)
SELECT 182182,-11 UNION ALL
SELECT 182387,-11 UNION ALL
SELECT 182388, -8 UNION ALL
SELECT 182388, -7 UNION ALL
SELECT 182463,-10 UNION ALL
SELECT 182463, -8 UNION ALL
SELECT 182463, -7 UNION ALL
SELECT 182645,-12 UNION ALL
SELECT 182645, -8 UNION ALL
SELECT 182645, -7
Then, using a bit of dynamic SQL, we can avoid the cursor... and it's nasty fast...
--===== Declare the variables to hold the parts of the dynamic SQL
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
DECLARE @SQL3 VARCHAR(8000)
--===== Create the dynamic SQL required to solve the problem
SELECT @SQL1 = 'SELECT ' + CHAR(10)
+ 'Applicant_User_ID,' + CHAR(10)
SELECT @SQL2 = ISNULL(@SQL2 + ',' + CHAR(10),'')
+ 'SUM(CASE WHEN Attribute_ID = '
+ STR(Attribute_ID,5)
+ ' THEN 1 ELSE 0 END) AS '
+ QUOTENAME(Description)
FROM #Attribute
ORDER BY Sort_Order
SELECT @SQL3 = 'FROM #Applicant_User' + CHAR(10)
+ 'GROUP BY Applicant_User_ID' + CHAR(10)
+ 'ORDER BY Applicant_User_ID'
--===== Uncomment the following line if you want to see what the SQL looks like
--PRINT @SQL1+@SQL2+@SQL3
--===== Execute the dynamic SQL to solve the problem
EXEC (@SQL1+@SQL2+@SQL3)
... and the output looks like this...
Applicant_User_ID Not applying P essay received P essay q sent P Invite P Deny P Recipient P Offer P Waitlist P Hold
----------------- ------------ ---------------- -------------- ----------- ----------- ----------- ----------- ----------- -----------
182182 1 0 0 0 0 0 0 0 0
182387 1 0 0 0 0 0 0 0 0
182388 0 1 1 0 0 0 0 0 0
182463 0 1 1 0 0 0 0 0 0
182645 0 1 1 0 0 0 0 0 0
Since you're "new" to the forum, lemme tell ya that to copy code from the light purple code windows, you need to put your cursor just above the window, click and drag to just after the code window, then copy that selection. Paste it into MS Word, do a Search'n'Replace to replace ^l with ^p, then copy and paste that into your SQL Server window. All formatting will be preserved that way.
Of course, you will need to change all the #tablename references to you real tables.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 19, 2007 at 1:23 pm
Jeff and Marcus - Many thanks, this concept put me on the path I (think I) was looking for.
But now I am running into another problem; the code is cutting off at 4000 characters. I assume this is b/c of the 8k page limitation, but is there some way around this? Should I use a cursor as Marcus did?
Marcus - I actually don't want multiple rows, that's one of the issues I was working through, how to get 1 record per applicant, with all of their data...
Current status:
/*
--===== Deletes, then Builds the Attributes_Array table (essentially an array shell)
Drop Table Attributes_Array
--===== Declare the variables to hold the parts of the dynamic SQL
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
DECLARE @SQL3 VARCHAR(8000)
DECLARE @SQL4 VARCHAR(8000)
--===== Create the dynamic SQL required to solve the problem
SELECT @SQL1 = 'SELECT ' + CHAR(10) + 'null as Applicant_User_ID,' + CHAR(10)
SELECT @SQL2 = ISNULL(@SQL2 + ',' + CHAR(10),'')
+ ' null AS '
+ QUOTENAME(Description)
FROM General_Attributes
ORDER BY Attribute_ID Desc
SELECT @SQL3 = CHAR(10)+ 'INTO Attributes_Array' + CHAR(10)
SELECT @SQL4 = 'FROM General_Attributes' + CHAR(10)
--===== Uncomment the following line if you want to see what the SQL looks like
-- PRINT @SQL1+@SQL2+@SQL3+@SQL4
--===== Execute the dynamic SQL
EXEC (@SQL1+@SQL2+@SQL3+@SQL4)
-- Delete the extra junk fields created
Delete from Attributes_Array
-- Proof of concept
-- Select * FROM Attributes_Array
*/
--=====add values to the array
DECLARE @SQL6 VARCHAR(8000)
DECLARE @SQL7 VARCHAR(8000)
DECLARE @SQL8 VARCHAR(8000)
-- used for second section
--===== Create the dynamic SQL required to solve the problem
SELECT @SQL6 = ISNULL(@SQL6,'')
+ 'Update Attributes_Array ' --+ CHAR(10)
+ 'SET ' + QUOTENAME(Description) + '=1' --+ CHAR(10)
+ 'WHERE Applicant_user_id=' + QUOTENAME(Applicant_user_id)--+ CHAR(10)
FROM Applicant_Attributes
LEFT OUTER JOIN General_Attributes ON Applicant_Attributes.attribute_id = General_Attributes.attribute_id
SELECT @SQL7 = CHAR(10)+ 'INTO Attributes_Array' + CHAR(10)
SELECT @SQL8 = 'FROM General_Attributes' + CHAR(10)
--===== Uncomment the following line if you want to see what the SQL looks like
PRINT @SQL6+@SQL7+@SQL8
--===== Execute the dynamic SQL
-- EXEC (@SQL6+@SQL7+@SQL8)
-- Proof of concept
-- Select * FROM Attributes_Array
FWIW - All I am trying to do is create an array of the data. If there is a more efficient (aka better) way to go about this, I am all ears. From what I've read, SQL2K does not have a built in function for this - does SQL2005?
December 19, 2007 at 1:35 pm
How are you viewing the code? If it's in Query Analyzer, it may just be a view setting under Tools Options.
You can concatenate about 256 8k variables for an execute so nothing should be getting cut-off.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 19, 2007 at 1:50 pm
QA - I did poke through the options earlier, but I didn't see anything on the results tab that looked like it would restrict the output. FWIW, I have not changed many of the default values...
@sql 6 is the fatty - that is the variable that cuts off at 4000 characters - the other variables print out just fine.
If I put the code into a stored procedure and call it some other way, that would eliminate QA as the possible issue, yes?
December 19, 2007 at 2:03 pm
Yeah, you could... but who cares? So long as the code executes correctly, you may not need to see it.
Under {Tools}{Options}, click on the [Results] tab and see what the {Maximum characters per column} setting is set to. That may be your culprit.
The real key for you print statement is that you're doing ...
PRINT @SQL6+@SQL7+@SQL8
Try just
PRINT @SQL6
PRINT @SQL7
PRINT @SQL8
Otherwise, change the exec to create a stored procedure using the variables and look at that... I'll many times have it make a "Temp" stored procedure that has a name that begins with the "#" sign just like a temp table... they act the same way so far as a session goes, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 19, 2007 at 4:59 pm
Jeff - thanks again for the help/advice.
I only care to the degree I can eliminate an error source. You mentioned QA may be the issue, so I wanted to know how to eliminate that possible error source.
Changed the maximum characters per column on the QA results tab to 800 then 8000 - no change. Also changed the print line from one combined to three separate print statements, same result.
I'll pursue the sp solution next, but just so I can eliminate myself as the issue, here's the code to create the full tables and the code to create the problem.
Create tables in Tempdb
USE tempdb
GO
if exists (select * from tempdb.dbo.sysobjects where name like 'General_Attributes%')
drop table General_Attributes
if exists (select * from tempdb.dbo.sysobjects where name like 'Applicant_Attributes%')
drop table Applicant_Attributes
CREATE TABLE [dbo].[General_Attributes] (
[attribute_id] [int] NOT NULL ,
[description] [varchar] (100)
)
CREATE TABLE [dbo].[Applicant_Attributes] (
[applicant_user_id] [int] NOT NULL ,
[attribute_id] [int] NOT NULL
)
Create Datasets (broken up into many insert statements to eliminate buffer overflow issue)
-- Insert General_Attributes Data
INSERT INTO General_Attributes
(attribute_id, description)
SELECT '-14','Not applying for Park fellowship' UNION ALL
SELECT '-13','5 - Do Not Rec' UNION ALL
SELECT '-12','4 - Rec with Res' UNION ALL
SELECT '-11','3 - Rec' UNION ALL
SELECT '-10','2 - Rec with Conf' UNION ALL
SELECT '-9','1 - Hi Rec' UNION ALL
SELECT '-8','P essay rec' UNION ALL
SELECT '-7','P essay s' UNION ALL
SELECT '-6','P I' UNION ALL
SELECT '-5','P D' UNION ALL
SELECT '-4','P Rec' UNION ALL
SELECT '-3','P O' UNION ALL
SELECT '-2','P W'
-- Insert Applicant_attributes
INSERT INTO Applicant_Attributes
(Applicant_user_id, Attribute_id)
SELECT '182182','-11' UNION ALL
SELECT '182387','-11' UNION ALL
SELECT '182388','-8' UNION ALL
SELECT '182388','-7' UNION ALL
SELECT '182463','-10' UNION ALL
SELECT '182463','-8' UNION ALL
SELECT '182463','-7' UNION ALL
SELECT '182645','-12' UNION ALL
SELECT '182645','-8' UNION ALL
SELECT '182645','-7' UNION ALL
SELECT '182664','-7' UNION ALL
SELECT '182772','-8' UNION ALL
SELECT '182772','-7' UNION ALL
SELECT '182825','-12' UNION ALL
SELECT '182827','-7' UNION ALL
SELECT '182842','-10' UNION ALL
SELECT '182842','-8' UNION ALL
SELECT '182842','-7' UNION ALL
SELECT '182842','-6' UNION ALL
SELECT '182948','-10' UNION ALL
SELECT '182949','-11' UNION ALL
SELECT '182949','-8' UNION ALL
SELECT '182949','-7' UNION ALL
SELECT '182978','-13' UNION ALL
SELECT '183196','-12' UNION ALL
SELECT '183196','-8' UNION ALL
SELECT '183196','-7' UNION ALL
SELECT '183244','-8' UNION ALL
SELECT '183244','-7' UNION ALL
SELECT '183354','-11' UNION ALL
SELECT '183354','-8' UNION ALL
SELECT '183354','-7' UNION ALL
SELECT '183354','-6' UNION ALL
SELECT '183455','-8' UNION ALL
SELECT '183455','-7' UNION ALL
SELECT '183839','-8' UNION ALL
SELECT '183839','-7' UNION ALL
SELECT '183902','-10' UNION ALL
SELECT '183902','-8'
INSERT INTO Applicant_Attributes
(Applicant_user_id, Attribute_id)
SELECT '183902','-7' UNION ALL
SELECT '183902','-5' UNION ALL
SELECT '183910','-13' UNION ALL
SELECT '183910','-8' UNION ALL
SELECT '183910','-7' UNION ALL
SELECT '183939','-11' UNION ALL
SELECT '183985','-11' UNION ALL
SELECT '183985','-8' UNION ALL
SELECT '183985','-7' UNION ALL
SELECT '184168','-10' UNION ALL
SELECT '184168','-8' UNION ALL
SELECT '184168','-7' UNION ALL
SELECT '184168','-6' UNION ALL
SELECT '184170','-8' UNION ALL
SELECT '184170','-7' UNION ALL
SELECT '184246','-8' UNION ALL
SELECT '184246','-7' UNION ALL
SELECT '184377','-11' UNION ALL
SELECT '184377','-8' UNION ALL
SELECT '184377','-7' UNION ALL
SELECT '184377','-1' UNION ALL
SELECT '184388','-12' UNION ALL
SELECT '184414','-12' UNION ALL
SELECT '184470','-9' UNION ALL
SELECT '184999','-13' UNION ALL
SELECT '184999','-8' UNION ALL
SELECT '184999','-7' UNION ALL
SELECT '185012','-8' UNION ALL
SELECT '185012','-7' UNION ALL
SELECT '185020','-9' UNION ALL
SELECT '185020','-8' UNION ALL
SELECT '185020','-7' UNION ALL
SELECT '185020','-6' UNION ALL
SELECT '185040','-8' UNION ALL
SELECT '185040','-7' UNION ALL
SELECT '185117','-8' UNION ALL
SELECT '185117','-7' UNION ALL
SELECT '185226','-13' UNION ALL
SELECT '185359','-8' UNION ALL
SELECT '185359','-7' UNION ALL
SELECT '185422','-11' UNION ALL
SELECT '185422','-8' UNION ALL
SELECT '185422','-7' UNION ALL
SELECT '185473','-10' UNION ALL
SELECT '185579','-11' UNION ALL
SELECT '185579','-8' UNION ALL
SELECT '185579','-7' UNION ALL
SELECT '185623','-12' UNION ALL
SELECT '185672','-10' UNION ALL
SELECT '185672','-8' UNION ALL
SELECT '185672','-7' UNION ALL
SELECT '185672','-5' UNION ALL
SELECT '185688','-10' UNION ALL
SELECT '185761','-8' UNION ALL
SELECT '185761','-7' UNION ALL
SELECT '185764','-12' UNION ALL
SELECT '185817','-8' UNION ALL
SELECT '185817','-7' UNION ALL
SELECT '185819','-13' UNION ALL
SELECT '185819','-8' UNION ALL
SELECT '185819','-7' UNION ALL
SELECT '185839','-7' UNION ALL
SELECT '185957','-8' UNION ALL
SELECT '185957','-7' UNION ALL
SELECT '186002','-8' UNION ALL
SELECT '186002','-7' UNION ALL
SELECT '186049','-11' UNION ALL
SELECT '186049','-8' UNION ALL
SELECT '186049','-7' UNION ALL
SELECT '186049','-5' UNION ALL
SELECT '186196','-7' UNION ALL
SELECT '186237','-7' UNION ALL
SELECT '186238','-13' UNION ALL
SELECT '186493','-11' UNION ALL
SELECT '186493','-8' UNION ALL
SELECT '186493','-7' UNION ALL
SELECT '186514','-8' UNION ALL
SELECT '186514','-7' UNION ALL
SELECT '186588','-10' UNION ALL
SELECT '186615','-8' UNION ALL
SELECT '186615','-7' UNION ALL
SELECT '186639','-8' UNION ALL
SELECT '186639','-7' UNION ALL
SELECT '186667','-12' UNION ALL
SELECT '186808','-10' UNION ALL
SELECT '186820','-8' UNION ALL
SELECT '186820','-7' UNION ALL
SELECT '186908','-8' UNION ALL
SELECT '186908','-7' UNION ALL
SELECT '186943','-7' UNION ALL
SELECT '186972','-8' UNION ALL
SELECT '186972','-7' UNION ALL
SELECT '186994','-8' UNION ALL
SELECT '186994','-7' UNION ALL
SELECT '187001','-11' UNION ALL
SELECT '187101','-8' UNION ALL
SELECT '187101','-7' UNION ALL
SELECT '187284','-14' UNION ALL
SELECT '187284','-9' UNION ALL
SELECT '187284','-7' UNION ALL
SELECT '187347','-10' UNION ALL
SELECT '187347','-8' UNION ALL
SELECT '187347','-7' UNION ALL
SELECT '187347','-1' UNION ALL
SELECT '187574','-12' UNION ALL
SELECT '187574','-8' UNION ALL
SELECT '187574','-7' UNION ALL
SELECT '187627','-12' UNION ALL
SELECT '187630','-8' UNION ALL
SELECT '187630','-7' UNION ALL
SELECT '187673','-8' UNION ALL
SELECT '187673','-7' UNION ALL
SELECT '187677','-13' UNION ALL
SELECT '187677','-8' UNION ALL
SELECT '187677','-7' UNION ALL
SELECT '187841','-11' UNION ALL
SELECT '187841','-8' UNION ALL
SELECT '187841','-7' UNION ALL
SELECT '187841','-6' UNION ALL
SELECT '187921','-10' UNION ALL
SELECT '187922','-8' UNION ALL
SELECT '187922','-7' UNION ALL
SELECT '188013','-8' UNION ALL
SELECT '188013','-7' UNION ALL
SELECT '188246','-11' UNION ALL
SELECT '188246','-8' UNION ALL
SELECT '188246','-7' UNION ALL
SELECT '188430','-10' UNION ALL
SELECT '188430','-8' UNION ALL
SELECT '188430','-7' UNION ALL
SELECT '188430','-6' UNION ALL
SELECT '188588','-11' UNION ALL
SELECT '188705','-8' UNION ALL
SELECT '188705','-7' UNION ALL
SELECT '188719','-8' UNION ALL
SELECT '188719','-7' UNION ALL
SELECT '188740','-11' UNION ALL
SELECT '188740','-8'
INSERT INTO Applicant_Attributes
(Applicant_user_id, Attribute_id)
SELECT '188740','-7' UNION ALL
SELECT '188740','-5' UNION ALL
SELECT '188752','-8' UNION ALL
SELECT '188752','-7' UNION ALL
SELECT '188945','-11' UNION ALL
SELECT '188945','-7' UNION ALL
SELECT '189014','-12' UNION ALL
SELECT '189070','-11' UNION ALL
SELECT '189070','-8' UNION ALL
SELECT '189070','-7' UNION ALL
SELECT '189115','-8' UNION ALL
SELECT '189115','-7' UNION ALL
SELECT '189124','-12' UNION ALL
SELECT '189124','-7' UNION ALL
SELECT '189127','-10' UNION ALL
SELECT '189127','-8' UNION ALL
SELECT '189127','-7' UNION ALL
SELECT '189345','-10' UNION ALL
SELECT '189347','-10' UNION ALL
SELECT '189454','-12' UNION ALL
SELECT '189454','-8' UNION ALL
SELECT '189454','-7' UNION ALL
SELECT '189625','-9' UNION ALL
SELECT '189749','-8' UNION ALL
SELECT '189749','-7' UNION ALL
SELECT '189952','-13' UNION ALL
SELECT '189972','-10' UNION ALL
SELECT '189972','-8' UNION ALL
SELECT '189972','-7' UNION ALL
SELECT '189972','-6' UNION ALL
SELECT '190147','-10' UNION ALL
SELECT '190161','-10' UNION ALL
SELECT '190161','-8' UNION ALL
SELECT '190161','-7' UNION ALL
SELECT '190161','-6' UNION ALL
SELECT '190244','-11' UNION ALL
SELECT '190244','-8' UNION ALL
SELECT '190244','-7' UNION ALL
SELECT '190244','-5' UNION ALL
SELECT '190278','-11' UNION ALL
SELECT '190301','-8' UNION ALL
SELECT '190301','-7' UNION ALL
SELECT '190354','-8' UNION ALL
SELECT '190354','-7' UNION ALL
SELECT '190415','-10' UNION ALL
SELECT '190470','-8' UNION ALL
SELECT '190470','-7' UNION ALL
SELECT '190496','-10' UNION ALL
SELECT '190595','-10' UNION ALL
SELECT '190595','-8' UNION ALL
SELECT '190595','-7' UNION ALL
SELECT '190595','-6' UNION ALL
SELECT '190628','-11' UNION ALL
SELECT '190698','-14' UNION ALL
SELECT '190698','-7' UNION ALL
SELECT '190808','-10' UNION ALL
SELECT '190808','-8' UNION ALL
SELECT '190808','-7' UNION ALL
SELECT '190808','-1' UNION ALL
SELECT '190906','-10' UNION ALL
SELECT '191063','-10' UNION ALL
SELECT '191092','-10' UNION ALL
SELECT '191165','-8' UNION ALL
SELECT '191165','-7' UNION ALL
SELECT '191294','-8' UNION ALL
SELECT '191294','-7' UNION ALL
SELECT '191374','-11' UNION ALL
SELECT '191415','-7' UNION ALL
SELECT '191431','-9' UNION ALL
SELECT '191431','-8' UNION ALL
SELECT '191431','-7' UNION ALL
SELECT '191570','-13' UNION ALL
SELECT '191705','-12' UNION ALL
SELECT '191758','-8' UNION ALL
SELECT '191758','-7' UNION ALL
SELECT '191760','-10' UNION ALL
SELECT '191760','-8' UNION ALL
SELECT '191760','-7' UNION ALL
SELECT '191764','-8' UNION ALL
SELECT '191764','-7' UNION ALL
SELECT '191816','-11' UNION ALL
SELECT '191816','-8' UNION ALL
SELECT '191816','-7' UNION ALL
SELECT '191816','-6' UNION ALL
SELECT '192000','-11' UNION ALL
SELECT '192000','-8' UNION ALL
SELECT '192000','-7' UNION ALL
SELECT '192000','-1' UNION ALL
SELECT '192005','-8' UNION ALL
SELECT '192005','-7' UNION ALL
SELECT '192011','-8' UNION ALL
SELECT '192011','-7' UNION ALL
SELECT '192118','-8' UNION ALL
SELECT '192118','-7' UNION ALL
SELECT '192118','-1' UNION ALL
SELECT '192144','-10' UNION ALL
SELECT '192187','-11' UNION ALL
SELECT '192188','-8' UNION ALL
SELECT '192188','-7' UNION ALL
SELECT '192492','-8' UNION ALL
SELECT '192492','-7' UNION ALL
SELECT '192623','-11' UNION ALL
SELECT '192623','-8' UNION ALL
SELECT '192623','-7' UNION ALL
SELECT '192623','-6' UNION ALL
SELECT '192628','-8' UNION ALL
SELECT '192628','-7' UNION ALL
SELECT '192701','-11' UNION ALL
SELECT '192830','-12' UNION ALL
SELECT '192830','-8' UNION ALL
SELECT '192830','-7' UNION ALL
SELECT '192987','-13' UNION ALL
SELECT '192987','-8' UNION ALL
SELECT '192987','-7' UNION ALL
SELECT '193012','-10' UNION ALL
SELECT '193012','-8' UNION ALL
SELECT '193012','-7' UNION ALL
SELECT '193012','-5' UNION ALL
SELECT '193030','-10' UNION ALL
SELECT '193030','-8' UNION ALL
SELECT '193030','-7' UNION ALL
SELECT '193030','-1' UNION ALL
SELECT '193050','-10' UNION ALL
SELECT '193050','-8' UNION ALL
SELECT '193050','-7' UNION ALL
SELECT '193050','-6' UNION ALL
SELECT '193202','-14' UNION ALL
SELECT '193202','-7' UNION ALL
SELECT '193258','-8' UNION ALL
SELECT '193258','-7' UNION ALL
SELECT '193347','-12' UNION ALL
SELECT '193357','-8' UNION ALL
SELECT '193357','-7' UNION ALL
SELECT '193401','-8' UNION ALL
SELECT '193401','-7' UNION ALL
SELECT '193419','-11' UNION ALL
SELECT '193419','-8' UNION ALL
SELECT '193419','-7' UNION ALL
SELECT '193661','-8' UNION ALL
SELECT '193661','-7' UNION ALL
SELECT '193666','-13' UNION ALL
SELECT '193666','-8' UNION ALL
SELECT '193666','-7' UNION ALL
SELECT '193698','-8' UNION ALL
SELECT '193698','-7' UNION ALL
SELECT '193782','-8' UNION ALL
SELECT '193782','-7' UNION ALL
SELECT '193851','-8' UNION ALL
SELECT '193851','-7' UNION ALL
SELECT '193855','-8' UNION ALL
SELECT '193855','-7' UNION ALL
SELECT '194208','-12' UNION ALL
SELECT '194209','-9' UNION ALL
SELECT '194213','-12' UNION ALL
SELECT '194217','-12' UNION ALL
SELECT '194217','-8' UNION ALL
SELECT '194217','-7' UNION ALL
SELECT '194218','-12' UNION ALL
SELECT '194219','-11' UNION ALL
SELECT '194219','-8' UNION ALL
SELECT '194219','-7' UNION ALL
SELECT '194220','-8' UNION ALL
SELECT '194220','-7' UNION ALL
SELECT '194223','-12' UNION ALL
SELECT '194223','-8' UNION ALL
SELECT '194223','-7' UNION ALL
SELECT '194225','-11' UNION ALL
SELECT '194226','-10' UNION ALL
SELECT '194227','-11' UNION ALL
SELECT '194227','-8' UNION ALL
SELECT '194227','-7' UNION ALL
SELECT '194227','-6' UNION ALL
SELECT '194227','-1' UNION ALL
SELECT '194232','-7' UNION ALL
SELECT '194233','-10' UNION ALL
SELECT '194233','-8' UNION ALL
SELECT '194233','-7' UNION ALL
SELECT '194233','-1' UNION ALL
SELECT '194235','-8' UNION ALL
SELECT '194235','-7' UNION ALL
SELECT '194236','-12' UNION ALL
SELECT '194236','-8' UNION ALL
SELECT '194236','-7' UNION ALL
SELECT '194237','-11' UNION ALL
SELECT '194238','-8' UNION ALL
SELECT '194238','-7' UNION ALL
SELECT '194239','-10' UNION ALL
SELECT '194240','-8' UNION ALL
SELECT '194240','-7' UNION ALL
SELECT '194241','-11' UNION ALL
SELECT '194243','-11' UNION ALL
SELECT '194244','-11' UNION ALL
SELECT '194245','-8' UNION ALL
SELECT '194245','-7' UNION ALL
SELECT '194251','-8' UNION ALL
SELECT '194251','-7' UNION ALL
SELECT '194253','-10' UNION ALL
SELECT '194253','-8' UNION ALL
SELECT '194253','-7' UNION ALL
SELECT '194253','-5' UNION ALL
SELECT '194258','-12' UNION ALL
SELECT '194258','-8' UNION ALL
SELECT '194258','-7' UNION ALL
SELECT '194259','-8' UNION ALL
SELECT '194259','-7' UNION ALL
SELECT '194264','-10' UNION ALL
SELECT '194268','-13' UNION ALL
SELECT '194268','-8' UNION ALL
SELECT '194268','-7' UNION ALL
SELECT '194273','-8' UNION ALL
SELECT '194273','-7' UNION ALL
SELECT '194276','-8' UNION ALL
SELECT '194276','-7' UNION ALL
SELECT '194277','-12' UNION ALL
SELECT '194277','-8' UNION ALL
SELECT '194277','-7' UNION ALL
SELECT '194277','-1' UNION ALL
SELECT '194278','-9' UNION ALL
SELECT '194278','-8' UNION ALL
SELECT '194278','-7' UNION ALL
SELECT '194278','-6' UNION ALL
SELECT '194279','-12' UNION ALL
SELECT '194279','-8' UNION ALL
SELECT '194279','-7' UNION ALL
SELECT '194279','-5' UNION ALL
SELECT '194280','-8' UNION ALL
SELECT '194280','-7' UNION ALL
SELECT '194281','-8' UNION ALL
SELECT '194281','-7' UNION ALL
SELECT '194283','-10' UNION ALL
SELECT '194283','-8' UNION ALL
SELECT '194283','-7' UNION ALL
SELECT '194284','-12' UNION ALL
SELECT '194284','-8' UNION ALL
SELECT '194284','-7' UNION ALL
SELECT '194285','-13' UNION ALL
SELECT '194288','-9' UNION ALL
SELECT '194288','-8' UNION ALL
SELECT '194288','-7' UNION ALL
SELECT '194288','-6' UNION ALL
SELECT '194291','-11' UNION ALL
SELECT '194295','-8' UNION ALL
SELECT '194295','-7'
INSERT INTO Applicant_Attributes
(Applicant_user_id, Attribute_id)
SELECT '194298','-8' UNION ALL
SELECT '194298','-7' UNION ALL
SELECT '194299','-8' UNION ALL
SELECT '194299','-7' UNION ALL
SELECT '194303','-11' UNION ALL
SELECT '194306','-7' UNION ALL
SELECT '194307','-8' UNION ALL
SELECT '194307','-7' UNION ALL
SELECT '194310','-10' UNION ALL
SELECT '194310','-8' UNION ALL
SELECT '194310','-7' UNION ALL
SELECT '194310','-1' UNION ALL
SELECT '194313','-14' UNION ALL
SELECT '194313','-7' UNION ALL
SELECT '194314','-11' UNION ALL
SELECT '194314','-8' UNION ALL
SELECT '194314','-7' UNION ALL
SELECT '194315','-8' UNION ALL
SELECT '194315','-7' UNION ALL
SELECT '194316','-8' UNION ALL
SELECT '194316','-7' UNION ALL
SELECT '194322','-14' UNION ALL
SELECT '194322','-7' UNION ALL
SELECT '194326','-12' UNION ALL
SELECT '194330','-12' UNION ALL
SELECT '194330','-8' UNION ALL
SELECT '194330','-7' UNION ALL
SELECT '194339','-8' UNION ALL
SELECT '194339','-7' UNION ALL
SELECT '194348','-10' UNION ALL
SELECT '194358','-9' UNION ALL
SELECT '194358','-8' UNION ALL
SELECT '194358','-7' UNION ALL
SELECT '194358','-1' UNION ALL
SELECT '194359','-11' UNION ALL
SELECT '194367','-13' UNION ALL
SELECT '194371','-10' UNION ALL
SELECT '194374','-12' UNION ALL
SELECT '194377','-14' UNION ALL
SELECT '194377','-7' UNION ALL
SELECT '194381','-13' UNION ALL
SELECT '194385','-11' UNION ALL
SELECT '194385','-8' UNION ALL
SELECT '194385','-7' UNION ALL
SELECT '194385','-5' UNION ALL
SELECT '194387','-11' UNION ALL
SELECT '194387','-8' UNION ALL
SELECT '194387','-7' UNION ALL
SELECT '194389','-13' UNION ALL
SELECT '194390','-9' UNION ALL
SELECT '194390','-8' UNION ALL
SELECT '194390','-7' UNION ALL
SELECT '194390','-6' UNION ALL
SELECT '194393','-8' UNION ALL
SELECT '194393','-7' UNION ALL
SELECT '194397','-10' UNION ALL
SELECT '194400','-8' UNION ALL
SELECT '194400','-7' UNION ALL
SELECT '194403','-11' UNION ALL
SELECT '194403','-8' UNION ALL
SELECT '194403','-7' UNION ALL
SELECT '194403','-6' UNION ALL
SELECT '194404','-8' UNION ALL
SELECT '194404','-7' UNION ALL
SELECT '194406','-8' UNION ALL
SELECT '194406','-7' UNION ALL
SELECT '194408','-12' UNION ALL
SELECT '194408','-8' UNION ALL
SELECT '194408','-7' UNION ALL
SELECT '194412','-9' UNION ALL
SELECT '194412','-8' UNION ALL
SELECT '194412','-7' UNION ALL
SELECT '194412','-6' UNION ALL
SELECT '194413','-12' UNION ALL
SELECT '194416','-8' UNION ALL
SELECT '194416','-7' UNION ALL
SELECT '194418','-10' UNION ALL
SELECT '194418','-8' UNION ALL
SELECT '194418','-7' UNION ALL
SELECT '194418','-6' UNION ALL
SELECT '194423','-12' UNION ALL
SELECT '194425','-8' UNION ALL
SELECT '194425','-7' UNION ALL
SELECT '194426','-12' UNION ALL
SELECT '194429','-8' UNION ALL
SELECT '194429','-7' UNION ALL
SELECT '194430','-8' UNION ALL
SELECT '194430','-7' UNION ALL
SELECT '194431','-11' UNION ALL
SELECT '194434','-10' UNION ALL
SELECT '194438','-8' UNION ALL
SELECT '194438','-7' UNION ALL
SELECT '194450','-11' UNION ALL
SELECT '194452','-11' UNION ALL
SELECT '194452','-8' UNION ALL
SELECT '194452','-7' UNION ALL
SELECT '194457','-10' UNION ALL
SELECT '194461','-8' UNION ALL
SELECT '194461','-7' UNION ALL
SELECT '194464','-8' UNION ALL
SELECT '194464','-7' UNION ALL
SELECT '194466','-14' UNION ALL
SELECT '194466','-7' UNION ALL
SELECT '194467','-8' UNION ALL
SELECT '194467','-7' UNION ALL
SELECT '194472','-12' UNION ALL
SELECT '194480','-12' UNION ALL
SELECT '194480','-8' UNION ALL
SELECT '194480','-7' UNION ALL
SELECT '194480','-5' UNION ALL
SELECT '194488','-8' UNION ALL
SELECT '194488','-7' UNION ALL
SELECT '194490','-10' UNION ALL
SELECT '194490','-8' UNION ALL
SELECT '194490','-7' UNION ALL
SELECT '194490','-5' UNION ALL
SELECT '194494','-11' UNION ALL
SELECT '194508','-11' UNION ALL
SELECT '194525','-9' UNION ALL
SELECT '194525','-8' UNION ALL
SELECT '194525','-7' UNION ALL
SELECT '194525','-6' UNION ALL
SELECT '194530','-12' UNION ALL
SELECT '194534','-9' UNION ALL
SELECT '194534','-8' UNION ALL
SELECT '194534','-7' UNION ALL
SELECT '194538','-7' UNION ALL
SELECT '194545','-7' UNION ALL
SELECT '194547','-8' UNION ALL
SELECT '194547','-7' UNION ALL
SELECT '194553','-8' UNION ALL
SELECT '194553','-7' UNION ALL
SELECT '194558','-9' UNION ALL
SELECT '194559','-10' UNION ALL
SELECT '194563','-11' UNION ALL
SELECT '194569','-12' UNION ALL
SELECT '194585','-8'
INSERT INTO Applicant_Attributes
(Applicant_user_id, Attribute_id)
SELECT '194585','-7' UNION ALL
SELECT '194596','-12' UNION ALL
SELECT '194596','-8' UNION ALL
SELECT '194596','-7' UNION ALL
SELECT '194596','-6' UNION ALL
SELECT '194597','-10' UNION ALL
SELECT '194604','-8' UNION ALL
SELECT '194604','-7' UNION ALL
SELECT '194605','-12' UNION ALL
SELECT '194605','-8' UNION ALL
SELECT '194605','-7' UNION ALL
SELECT '194606','-8' UNION ALL
SELECT '194606','-7' UNION ALL
SELECT '194607','-8' UNION ALL
SELECT '194607','-7' UNION ALL
SELECT '194614','-12' UNION ALL
SELECT '194615','-11' UNION ALL
SELECT '194615','-8' UNION ALL
SELECT '194615','-7' UNION ALL
SELECT '194619','-11' UNION ALL
SELECT '194619','-8' UNION ALL
SELECT '194619','-7' UNION ALL
SELECT '194619','-1' UNION ALL
SELECT '194620','-8' UNION ALL
SELECT '194620','-7' UNION ALL
SELECT '194649','-12' UNION ALL
SELECT '194655','-7' UNION ALL
SELECT '194658','-8' UNION ALL
SELECT '194658','-7' UNION ALL
SELECT '194678','-8' UNION ALL
SELECT '194678','-7' UNION ALL
SELECT '194682','-8' UNION ALL
SELECT '194682','-7' UNION ALL
SELECT '194683','-10' UNION ALL
SELECT '194683','-8' UNION ALL
SELECT '194683','-7' UNION ALL
SELECT '194683','-1' UNION ALL
SELECT '194685','-11' UNION ALL
SELECT '194685','-8' UNION ALL
SELECT '194685','-7' UNION ALL
SELECT '194694','-10' UNION ALL
SELECT '194695','-12' UNION ALL
SELECT '194695','-8' UNION ALL
SELECT '194695','-7' UNION ALL
SELECT '194695','-1' UNION ALL
SELECT '194699','-8' UNION ALL
SELECT '194699','-7' UNION ALL
SELECT '194701','-8' UNION ALL
SELECT '194701','-7' UNION ALL
SELECT '194702','-8' UNION ALL
SELECT '194702','-7' UNION ALL
SELECT '194708','-13' UNION ALL
SELECT '194708','-8' UNION ALL
SELECT '194708','-7' UNION ALL
SELECT '194709','-8' UNION ALL
SELECT '194709','-7' UNION ALL
SELECT '194710','-10' UNION ALL
SELECT '194710','-8' UNION ALL
SELECT '194710','-7' UNION ALL
SELECT '194715','-7' UNION ALL
SELECT '194718','-8' UNION ALL
SELECT '194718','-7' UNION ALL
SELECT '194719','-10' UNION ALL
SELECT '194719','-8' UNION ALL
SELECT '194719','-7' UNION ALL
SELECT '194719','-6' UNION ALL
SELECT '194722','-13' UNION ALL
SELECT '194724','-8' UNION ALL
SELECT '194724','-7' UNION ALL
SELECT '194725','-8' UNION ALL
SELECT '194725','-7' UNION ALL
SELECT '194727','-13' UNION ALL
SELECT '194727','-8' UNION ALL
SELECT '194727','-7' UNION ALL
SELECT '194733','-8' UNION ALL
SELECT '194733','-7' UNION ALL
SELECT '194734','-8' UNION ALL
SELECT '194734','-7' UNION ALL
SELECT '194736','-12' UNION ALL
SELECT '194736','-8' UNION ALL
SELECT '194736','-7' UNION ALL
SELECT '194738','-10' UNION ALL
SELECT '194738','-8' UNION ALL
SELECT '194738','-7' UNION ALL
SELECT '194738','-6' UNION ALL
SELECT '194742','-8' UNION ALL
SELECT '194742','-7' UNION ALL
SELECT '194748','-10' UNION ALL
SELECT '194759','-13' UNION ALL
SELECT '194760','-10' UNION ALL
SELECT '194761','-8' UNION ALL
SELECT '194761','-7' UNION ALL
SELECT '194762','-8' UNION ALL
SELECT '194762','-7' UNION ALL
SELECT '194765','-10' UNION ALL
SELECT '194765','-8' UNION ALL
SELECT '194765','-7' UNION ALL
SELECT '194765','-6' UNION ALL
SELECT '194768','-12' UNION ALL
SELECT '194768','-8' UNION ALL
SELECT '194768','-7' UNION ALL
SELECT '194769','-13' UNION ALL
SELECT '194772','-11' UNION ALL
SELECT '194780','-8' UNION ALL
SELECT '194780','-7' UNION ALL
SELECT '194782','-8' UNION ALL
SELECT '194782','-7' UNION ALL
SELECT '194783','-8'
INSERT INTO Applicant_Attributes
(Applicant_user_id, Attribute_id)
SELECT '194783','-7' UNION ALL
SELECT '194786','-12' UNION ALL
SELECT '194788','-8' UNION ALL
SELECT '194788','-7' UNION ALL
SELECT '194791','-8' UNION ALL
SELECT '194791','-7' UNION ALL
SELECT '194793','-11' UNION ALL
SELECT '194794','-12' UNION ALL
SELECT '194800','-11' UNION ALL
SELECT '194800','-8' UNION ALL
SELECT '194800','-7' UNION ALL
SELECT '194804','-14' UNION ALL
SELECT '194804','-10' UNION ALL
SELECT '194804','-7' UNION ALL
SELECT '194805','-13' UNION ALL
SELECT '194807','-8' UNION ALL
SELECT '194807','-7' UNION ALL
SELECT '194811','-14' UNION ALL
SELECT '194811','-7' UNION ALL
SELECT '194816','-12' UNION ALL
SELECT '194820','-11' UNION ALL
SELECT '194823','-8' UNION ALL
SELECT '194823','-7' UNION ALL
SELECT '194844','-8' UNION ALL
SELECT '194844','-7' UNION ALL
SELECT '194845','-10' UNION ALL
SELECT '194851','-10' UNION ALL
SELECT '194853','-14' UNION ALL
SELECT '194853','-7' UNION ALL
SELECT '194857','-10' UNION ALL
SELECT '194865','-8' UNION ALL
SELECT '194865','-7' UNION ALL
SELECT '194872','-13' UNION ALL
SELECT '194875','-8' UNION ALL
SELECT '194875','-7' UNION ALL
SELECT '194881','-11' UNION ALL
SELECT '194881','-8' UNION ALL
SELECT '194881','-7' UNION ALL
SELECT '194881','-5' UNION ALL
SELECT '194890','-8' UNION ALL
SELECT '194890','-7' UNION ALL
SELECT '194893','-11' UNION ALL
SELECT '194893','-8' UNION ALL
SELECT '194893','-7' UNION ALL
SELECT '194893','-1' UNION ALL
SELECT '194897','-11' UNION ALL
SELECT '194897','-8' UNION ALL
SELECT '194897','-7' UNION ALL
SELECT '194897','-6' UNION ALL
SELECT '194905','-8' UNION ALL
SELECT '194905','-7' UNION ALL
SELECT '194914','-9' UNION ALL
SELECT '194921','-11' UNION ALL
SELECT '194923','-10' UNION ALL
SELECT '194923','-8' UNION ALL
SELECT '194923','-7' UNION ALL
SELECT '194923','-6' UNION ALL
SELECT '194930','-12' UNION ALL
SELECT '194931','-12' UNION ALL
SELECT '194932','-8' UNION ALL
SELECT '194932','-7' UNION ALL
SELECT '194933','-13' UNION ALL
SELECT '194933','-8' UNION ALL
SELECT '194933','-7' UNION ALL
SELECT '194934','-7' UNION ALL
SELECT '194940','-12' UNION ALL
SELECT '194940','-8' UNION ALL
SELECT '194940','-7' UNION ALL
SELECT '194940','-1' UNION ALL
SELECT '194943','-8' UNION ALL
SELECT '194943','-7' UNION ALL
SELECT '194955','-10' UNION ALL
SELECT '194956','-7' UNION ALL
SELECT '194973','-11' UNION ALL
SELECT '194978','-14' UNION ALL
SELECT '194978','-11' UNION ALL
SELECT '194978','-7' UNION ALL
SELECT '194983','-8' UNION ALL
SELECT '194983','-7' UNION ALL
SELECT '194988','-8' UNION ALL
SELECT '194988','-7' UNION ALL
SELECT '194990','-7' UNION ALL
SELECT '194999','-8' UNION ALL
SELECT '194999','-7' UNION ALL
SELECT '195001','-13' UNION ALL
SELECT '195002','-10' UNION ALL
SELECT '195003','-14' UNION ALL
SELECT '195003','-7' UNION ALL
SELECT '195009','-13' UNION ALL
SELECT '195020','-8' UNION ALL
SELECT '195020','-7' UNION ALL
SELECT '195029','-10' UNION ALL
SELECT '195029','-8' UNION ALL
SELECT '195029','-7' UNION ALL
SELECT '195030','-11' UNION ALL
SELECT '195035','-8' UNION ALL
SELECT '195035','-7' UNION ALL
SELECT '195039','-12' UNION ALL
SELECT '195040','-8' UNION ALL
SELECT '195040','-7' UNION ALL
SELECT '195041','-10' UNION ALL
SELECT '195046','-11' UNION ALL
SELECT '195056','-10' UNION ALL
SELECT '195060','-8' UNION ALL
SELECT '195060','-7' UNION ALL
SELECT '195070','-8' UNION ALL
SELECT '195070','-7' UNION ALL
SELECT '195071','-10' UNION ALL
SELECT '195071','-8' UNION ALL
SELECT '195071','-7' UNION ALL
SELECT '195071','-6' UNION ALL
SELECT '195072','-8' UNION ALL
SELECT '195072','-7' UNION ALL
SELECT '195075','-12' UNION ALL
SELECT '195081','-8' UNION ALL
SELECT '195081','-7' UNION ALL
SELECT '195084','-7' UNION ALL
SELECT '195086','-8' UNION ALL
SELECT '195086','-7' UNION ALL
SELECT '195102','-11' UNION ALL
SELECT '195111','-11' UNION ALL
SELECT '195120','-8' UNION ALL
SELECT '195120','-7' UNION ALL
SELECT '195123','-11' UNION ALL
SELECT '195135','-8' UNION ALL
SELECT '195135','-7' UNION ALL
SELECT '195138','-8' UNION ALL
SELECT '195138','-7' UNION ALL
SELECT '195145','-11' UNION ALL
SELECT '195146','-8' UNION ALL
SELECT '195146','-7' UNION ALL
SELECT '195150','-11' UNION ALL
SELECT '195152','-12' UNION ALL
SELECT '195152','-8' UNION ALL
SELECT '195152','-7' UNION ALL
SELECT '195152','-1' UNION ALL
SELECT '195153','-8' UNION ALL
SELECT '195153','-7' UNION ALL
SELECT '195155','-12' UNION ALL
SELECT '195155','-8' UNION ALL
SELECT '195155','-7' UNION ALL
SELECT '195156','-12' UNION ALL
SELECT '195158','-10' UNION ALL
SELECT '195158','-7' UNION ALL
SELECT '195159','-8' UNION ALL
SELECT '195159','-7' UNION ALL
SELECT '195160','-11' UNION ALL
SELECT '195160','-8' UNION ALL
SELECT '195160','-7' UNION ALL
SELECT '195160','-5' UNION ALL
SELECT '195172','-11' UNION ALL
SELECT '195173','-11' UNION ALL
SELECT '195192','-8' UNION ALL
SELECT '195192','-7' UNION ALL
SELECT '195193','-11' UNION ALL
SELECT '195195','-13' UNION ALL
SELECT '195195','-8' UNION ALL
SELECT '195195','-7' UNION ALL
SELECT '195195','-5' UNION ALL
SELECT '195199','-11' UNION ALL
SELECT '195199','-8' UNION ALL
SELECT '195199','-7' UNION ALL
SELECT '195213','-8' UNION ALL
SELECT '195213','-7' UNION ALL
SELECT '195216','-14' UNION ALL
SELECT '195216','-7' UNION ALL
SELECT '195218','-10' UNION ALL
SELECT '195219','-8' UNION ALL
SELECT '195219','-7' UNION ALL
SELECT '195224','-11' UNION ALL
SELECT '195229','-13' UNION ALL
SELECT '195229','-8' UNION ALL
SELECT '195229','-7' UNION ALL
SELECT '195231','-12' UNION ALL
SELECT '195241','-11' UNION ALL
SELECT '195248','-12' UNION ALL
SELECT '195251','-13' UNION ALL
SELECT '195252','-8' UNION ALL
SELECT '195252','-7' UNION ALL
SELECT '195273','-8' UNION ALL
SELECT '195273','-7' UNION ALL
SELECT '195276','-7' UNION ALL
SELECT '195280','-8' UNION ALL
SELECT '195280','-7' UNION ALL
SELECT '195287','-8' UNION ALL
SELECT '195287','-7' UNION ALL
SELECT '195292','-11' UNION ALL
SELECT '195320','-14' UNION ALL
SELECT '195320','-7'
INSERT INTO Applicant_Attributes
(Applicant_user_id, Attribute_id)
SELECT '195325','-8' UNION ALL
SELECT '195325','-7' UNION ALL
SELECT '195330','-10' UNION ALL
SELECT '195330','-8' UNION ALL
SELECT '195330','-7' UNION ALL
SELECT '195330','-1' UNION ALL
SELECT '195334','-7' UNION ALL
SELECT '195356','-12' UNION ALL
SELECT '195367','-10' UNION ALL
SELECT '195367','-8' UNION ALL
SELECT '195367','-7' UNION ALL
SELECT '195381','-8' UNION ALL
SELECT '195381','-7' UNION ALL
SELECT '195403','-10' UNION ALL
SELECT '195405','-10' UNION ALL
SELECT '195406','-12' UNION ALL
SELECT '195414','-11' UNION ALL
SELECT '195421','-11' UNION ALL
SELECT '195421','-8' UNION ALL
SELECT '195421','-7' UNION ALL
SELECT '195422','-8' UNION ALL
SELECT '195422','-7' UNION ALL
SELECT '195435','-13' UNION ALL
SELECT '195438','-11' UNION ALL
SELECT '195442','-8' UNION ALL
SELECT '195442','-7' UNION ALL
SELECT '195447','-8' UNION ALL
SELECT '195447','-7' UNION ALL
SELECT '195453','-8' UNION ALL
SELECT '195453','-7' UNION ALL
SELECT '195456','-10' UNION ALL
SELECT '195464','-11' UNION ALL
SELECT '195465','-8' UNION ALL
SELECT '195465','-7' UNION ALL
SELECT '195468','-11' UNION ALL
SELECT '195475','-7' UNION ALL
SELECT '195477','-10' UNION ALL
SELECT '195482','-9' UNION ALL
SELECT '195506','-14' UNION ALL
SELECT '195506','-7' UNION ALL
SELECT '195509','-11' UNION ALL
SELECT '195509','-8' UNION ALL
SELECT '195509','-7' UNION ALL
SELECT '195511','-7' UNION ALL
SELECT '195513','-8' UNION ALL
SELECT '195513','-7' UNION ALL
SELECT '195514','-8' UNION ALL
SELECT '195514','-7' UNION ALL
SELECT '195515','-11' UNION ALL
SELECT '195516','-8' UNION ALL
SELECT '195516','-7' UNION ALL
SELECT '195519','-12' UNION ALL
SELECT '195532','-8' UNION ALL
SELECT '195532','-7' UNION ALL
SELECT '195537','-8' UNION ALL
SELECT '195537','-7' UNION ALL
SELECT '195546','-13' UNION ALL
SELECT '195551','-14' UNION ALL
SELECT '195551','-7' UNION ALL
SELECT '195554','-8' UNION ALL
SELECT '195554','-7' UNION ALL
SELECT '195569','-8' UNION ALL
SELECT '195569','-7' UNION ALL
SELECT '195570','-12' UNION ALL
SELECT '195577','-8' UNION ALL
SELECT '195577','-7' UNION ALL
SELECT '195579','-8' UNION ALL
SELECT '195579','-7' UNION ALL
SELECT '195582','-7' UNION ALL
SELECT '195593','-8' UNION ALL
SELECT '195593','-7' UNION ALL
SELECT '195594','-8' UNION ALL
SELECT '195594','-7' UNION ALL
SELECT '195596','-10' UNION ALL
SELECT '195596','-7' UNION ALL
SELECT '195599','-12' UNION ALL
SELECT '195603','-8' UNION ALL
SELECT '195603','-7' UNION ALL
SELECT '195637','-11' UNION ALL
SELECT '195637','-8' UNION ALL
SELECT '195637','-7' UNION ALL
SELECT '195641','-8' UNION ALL
SELECT '195641','-7' UNION ALL
SELECT '195642','-11' UNION ALL
SELECT '195642','-8' UNION ALL
SELECT '195642','-7' UNION ALL
SELECT '195642','-5' UNION ALL
SELECT '195643','-8' UNION ALL
SELECT '195643','-7' UNION ALL
SELECT '195658','-11' UNION ALL
SELECT '195662','-11' UNION ALL
SELECT '195663','-11' UNION ALL
SELECT '195694','-8' UNION ALL
SELECT '195694','-7' UNION ALL
SELECT '195696','-12' UNION ALL
SELECT '195700','-8' UNION ALL
SELECT '195700','-7' UNION ALL
SELECT '195720','-8' UNION ALL
SELECT '195720','-7' UNION ALL
SELECT '195724','-8' UNION ALL
SELECT '195724','-7' UNION ALL
SELECT '195725','-11' UNION ALL
SELECT '195735','-8' UNION ALL
SELECT '195735','-7' UNION ALL
SELECT '195756','-10' UNION ALL
SELECT '195757','-12' UNION ALL
SELECT '195757','-8' UNION ALL
SELECT '195757','-7' UNION ALL
SELECT '195758','-8' UNION ALL
SELECT '195758','-7' UNION ALL
SELECT '195769','-8' UNION ALL
SELECT '195769','-7' UNION ALL
SELECT '195770','-10' UNION ALL
SELECT '195770','-8' UNION ALL
SELECT '195770','-7' UNION ALL
SELECT '195789','-9' UNION ALL
SELECT '195794','-8' UNION ALL
SELECT '195794','-7' UNION ALL
SELECT '195797','-8' UNION ALL
SELECT '195797','-7' UNION ALL
SELECT '195805','-7' UNION ALL
SELECT '195821','-8' UNION ALL
SELECT '195821','-7' UNION ALL
SELECT '195823','-8' UNION ALL
SELECT '195823','-7' UNION ALL
SELECT '195826','-9' UNION ALL
SELECT '195843','-11' UNION ALL
SELECT '195844','-8' UNION ALL
SELECT '195844','-7' UNION ALL
SELECT '195849','-8' UNION ALL
SELECT '195849','-7' UNION ALL
SELECT '195855','-8' UNION ALL
SELECT '195855','-7' UNION ALL
SELECT '195863','-13' UNION ALL
SELECT '195869','-12' UNION ALL
SELECT '195869','-8'
INSERT INTO Applicant_Attributes
(Applicant_user_id, Attribute_id)
SELECT '195869','-7' UNION ALL
SELECT '195870','-8' UNION ALL
SELECT '195870','-7' UNION ALL
SELECT '195873','-8' UNION ALL
SELECT '195873','-7' UNION ALL
SELECT '195878','-8' UNION ALL
SELECT '195878','-7' UNION ALL
SELECT '195898','-11' UNION ALL
SELECT '195902','-7' UNION ALL
SELECT '195907','-10' UNION ALL
SELECT '195907','-7' UNION ALL
SELECT '195911','-8' UNION ALL
SELECT '195911','-7' UNION ALL
SELECT '195917','-14' UNION ALL
SELECT '195917','-7' UNION ALL
SELECT '195924','-9' UNION ALL
SELECT '195924','-8' UNION ALL
SELECT '195924','-7' UNION ALL
SELECT '195963','-11' UNION ALL
SELECT '195969','-8' UNION ALL
SELECT '195969','-7' UNION ALL
SELECT '195975','-8' UNION ALL
SELECT '195975','-7' UNION ALL
SELECT '195981','-14' UNION ALL
SELECT '195981','-7' UNION ALL
SELECT '195985','-10' UNION ALL
SELECT '195990','-13' UNION ALL
SELECT '196001','-9' UNION ALL
SELECT '196007','-8' UNION ALL
SELECT '196007','-7' UNION ALL
SELECT '196022','-11' UNION ALL
SELECT '196038','-10' UNION ALL
SELECT '196038','-8' UNION ALL
SELECT '196038','-7' UNION ALL
SELECT '196057','-8' UNION ALL
SELECT '196057','-7' UNION ALL
SELECT '196081','-8' UNION ALL
SELECT '196081','-7' UNION ALL
SELECT '196089','-10' UNION ALL
SELECT '196119','-7' UNION ALL
SELECT '196121','-8' UNION ALL
SELECT '196121','-7' UNION ALL
SELECT '196132','-7' UNION ALL
SELECT '196152','-7' UNION ALL
SELECT '196159','-8' UNION ALL
SELECT '196159','-7' UNION ALL
SELECT '196172','-8' UNION ALL
SELECT '196172','-7' UNION ALL
SELECT '196200','-10' UNION ALL
SELECT '196211','-7' UNION ALL
SELECT '196215','-7' UNION ALL
SELECT '196221','-8' UNION ALL
SELECT '196221','-7' UNION ALL
SELECT '196257','-8' UNION ALL
SELECT '196257','-7' UNION ALL
SELECT '196262','-8' UNION ALL
SELECT '196262','-7' UNION ALL
SELECT '196273','-7' UNION ALL
SELECT '196279','-7' UNION ALL
SELECT '196281','-7' UNION ALL
SELECT '196283','-10' UNION ALL
SELECT '196283','-8' UNION ALL
SELECT '196283','-7' UNION ALL
SELECT '196298','-8' UNION ALL
SELECT '196298','-7' UNION ALL
SELECT '196308','-8' UNION ALL
SELECT '196308','-7' UNION ALL
SELECT '196321','-8' UNION ALL
SELECT '196321','-7' UNION ALL
SELECT '196325','-8' UNION ALL
SELECT '196325','-7' UNION ALL
SELECT '196329','-8' UNION ALL
SELECT '196329','-7' UNION ALL
SELECT '196334','-8' UNION ALL
SELECT '196334','-7' UNION ALL
SELECT '196353','-8' UNION ALL
SELECT '196353','-7' UNION ALL
SELECT '196366','-8' UNION ALL
SELECT '196366','-7' UNION ALL
SELECT '196372','-8' UNION ALL
SELECT '196372','-7' UNION ALL
SELECT '196377','-8' UNION ALL
SELECT '196377','-7' UNION ALL
SELECT '196379','-7' UNION ALL
SELECT '196398','-8' UNION ALL
SELECT '196398','-7' UNION ALL
SELECT '196403','-8' UNION ALL
SELECT '196403','-7' UNION ALL
SELECT '196407','-7' UNION ALL
SELECT '196420','-8' UNION ALL
SELECT '196420','-7' UNION ALL
SELECT '196453','-8' UNION ALL
SELECT '196453','-7' UNION ALL
SELECT '196458','-8' UNION ALL
SELECT '196458','-7' UNION ALL
SELECT '196461','-8' UNION ALL
SELECT '196461','-7' UNION ALL
SELECT '196466','-8' UNION ALL
SELECT '196466','-7' UNION ALL
SELECT '196469','-8' UNION ALL
SELECT '196469','-7' UNION ALL
SELECT '196481','-8' UNION ALL
SELECT '196481','-7' UNION ALL
SELECT '196533','-8' UNION ALL
SELECT '196533','-7' UNION ALL
SELECT '196539','-8' UNION ALL
SELECT '196539','-7' UNION ALL
SELECT '196554','-8' UNION ALL
SELECT '196554','-7' UNION ALL
SELECT '196561','-8' UNION ALL
SELECT '196561','-7' UNION ALL
SELECT '196584','-8' UNION ALL
SELECT '196584','-7' UNION ALL
SELECT '196607','-7' UNION ALL
SELECT '196608','-8' UNION ALL
SELECT '196608','-7' UNION ALL
SELECT '196620','-8' UNION ALL
SELECT '196620','-7' UNION ALL
SELECT '196622','-7' UNION ALL
SELECT '196624','-8' UNION ALL
SELECT '196624','-7' UNION ALL
SELECT '196648','-8' UNION ALL
SELECT '196648','-7' UNION ALL
SELECT '196654','-8' UNION ALL
SELECT '196654','-7' UNION ALL
SELECT '196662','-8' UNION ALL
SELECT '196662','-7' UNION ALL
SELECT '196690','-7' UNION ALL
SELECT '196699','-8' UNION ALL
SELECT '196699','-7' UNION ALL
SELECT '196701','-10' UNION ALL
SELECT '196701','-8' UNION ALL
SELECT '196701','-7' UNION ALL
SELECT '196706','-8' UNION ALL
SELECT '196706','-7' UNION ALL
SELECT '196719','-8' UNION ALL
SELECT '196719','-7' UNION ALL
SELECT '196720','-8' UNION ALL
SELECT '196720','-7' UNION ALL
SELECT '196724','-8' UNION ALL
SELECT '196724','-7' UNION ALL
SELECT '196761','-8' UNION ALL
SELECT '196761','-7' UNION ALL
SELECT '196763','-14' UNION ALL
SELECT '196763','-7' UNION ALL
SELECT '196801','-8' UNION ALL
SELECT '196801','-7' UNION ALL
SELECT '196803','-8' UNION ALL
SELECT '196803','-7' UNION ALL
SELECT '196826','-8' UNION ALL
SELECT '196826','-7' UNION ALL
SELECT '196854','-8' UNION ALL
SELECT '196854','-7' UNION ALL
SELECT '196868','-7' UNION ALL
SELECT '196872','-14' UNION ALL
SELECT '196872','-7' UNION ALL
SELECT '196924','-8' UNION ALL
SELECT '196924','-7' UNION ALL
SELECT '196927','-8' UNION ALL
SELECT '196927','-7' UNION ALL
SELECT '196930','-8' UNION ALL
SELECT '196930','-7'
INSERT INTO Applicant_Attributes
(Applicant_user_id, Attribute_id)
SELECT '196952','-8' UNION ALL
SELECT '196952','-7' UNION ALL
SELECT '196966','-8' UNION ALL
SELECT '196966','-7' UNION ALL
SELECT '196972','-7' UNION ALL
SELECT '196977','-7' UNION ALL
SELECT '197005','-7' UNION ALL
SELECT '197049','-7' UNION ALL
SELECT '197061','-8' UNION ALL
SELECT '197061','-7' UNION ALL
SELECT '197063','-8' UNION ALL
SELECT '197063','-7' UNION ALL
SELECT '197073','-8' UNION ALL
SELECT '197073','-7' UNION ALL
SELECT '197075','-7' UNION ALL
SELECT '197080','-8' UNION ALL
SELECT '197080','-7' UNION ALL
SELECT '197125','-8' UNION ALL
SELECT '197125','-7' UNION ALL
SELECT '197173','-14' UNION ALL
SELECT '197173','-7' UNION ALL
SELECT '197174','-7' UNION ALL
SELECT '197187','-7' UNION ALL
SELECT '197219','-8' UNION ALL
SELECT '197219','-7' UNION ALL
SELECT '197226','-8' UNION ALL
SELECT '197226','-7' UNION ALL
SELECT '197242','-7' UNION ALL
SELECT '197245','-7' UNION ALL
SELECT '197247','-8' UNION ALL
SELECT '197247','-7' UNION ALL
SELECT '197264','-8' UNION ALL
SELECT '197264','-7' UNION ALL
SELECT '197284','-7' UNION ALL
SELECT '197324','-8' UNION ALL
SELECT '197324','-7' UNION ALL
SELECT '197329','-8' UNION ALL
SELECT '197329','-7' UNION ALL
SELECT '197338','-8' UNION ALL
SELECT '197338','-7' UNION ALL
SELECT '197361','-7' UNION ALL
SELECT '197370','-8' UNION ALL
SELECT '197370','-7' UNION ALL
SELECT '197390','-8' UNION ALL
SELECT '197390','-7' UNION ALL
SELECT '197399','-8' UNION ALL
SELECT '197399','-7' UNION ALL
SELECT '197453','-7' UNION ALL
SELECT '197463','-8' UNION ALL
SELECT '197463','-7' UNION ALL
SELECT '197480','-7' UNION ALL
SELECT '197500','-8' UNION ALL
SELECT '197500','-7' UNION ALL
SELECT '197506','-7' UNION ALL
SELECT '197990','-8' UNION ALL
SELECT '197990','-7' UNION ALL
SELECT '198438','-8' UNION ALL
SELECT '198438','-7' UNION ALL
SELECT '198557','-7'
/*
Select * FROM Applicant_Attributes -- should be 1211 rows
Select * FROM General_Attributes -- should be 13 rows
*/
Execute the Array building code
--===== Creates Baseline array table
if exists (select * from tempdb.dbo.sysobjects where name like 'Attributes_Array%')
drop table Attributes_Array
--===== Declare the variables to hold the parts of the dynamic SQL
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
DECLARE @SQL3 VARCHAR(8000)
DECLARE @SQL4 VARCHAR(8000)
--===== Create the dynamic SQL required to solve the problem
SELECT @SQL1 = 'SELECT ' + CHAR(10) + 'null as Applicant_User_ID,' + CHAR(10)
SELECT @SQL2 = ISNULL(@SQL2 + ',' + CHAR(10),'')
+ ' null AS '
+ QUOTENAME(Description)
FROM General_Attributes
ORDER BY Attribute_ID Desc
SELECT @SQL3 = CHAR(10)+ 'INTO Attributes_Array' + CHAR(10)
SELECT @SQL4 = 'FROM General_Attributes' + CHAR(10)
--===== Uncomment the following line if you want to see what the SQL looks like
-- PRINT @SQL1+@SQL2+@SQL3+@SQL4
--===== Execute the dynamic SQL
EXEC (@SQL1+@SQL2+@SQL3+@SQL4)
--===== Delete junk rows
DELETE FROM Attributes_array
-- Select * FROM attributes_array -- should return empty table
--=====insert applicant_user_id to array table (2nd dimension)
INSERT INTO Attributes_Array (Applicant_user_id)
SELECT DISTINCT Applicant_user_id
FROM Applicant_Attributes
-- Select * FROM Attributes_array
Here's where it gets fun - notice the "TOP 112"... as long at that is in there, the code works. I drop that or expand it, and the code breaks... FWIW, 112 rows returns 3824 characters.
--=====add values to the array
DECLARE @SQL6 VARCHAR(8000)
--===== Create the dynamic SQL required to solve the problem
SELECT TOP 112 @SQL6 = ISNULL(@SQL6,'')
+ 'Update Attributes_Array' + CHAR(10)
+ 'SET ' + QUOTENAME(Description) + '=1' + CHAR(10)
+ 'WHERE Applicant_user_id=' + CHAR(39) + RTRIM(Applicant_user_id) + CHAR(39) + CHAR(10)
FROM Applicant_Attributes
LEFT OUTER JOIN General_Attributes ON Applicant_Attributes.attribute_id = General_Attributes.attribute_id
--===== Uncomment the following line if you want to see what the SQL looks like
-- PRINT (@SQL6)
--===== Execute the dynamic SQL
EXEC (@SQL6)
-- Proof of concept
/* Select * FROM Attributes_Array WHERE
[Not applying for Park fellowship] is not null OR
[5 - Do Not Rec] is not null OR
[4 - Rec with Res] is not null OR
[3 - Rec] is not null OR
[2 - Rec with Conf] is not null OR
[1 - Hi Rec] is not null OR
[P essay rec] is not null OR
[P essay s] is not null OR
[P I] is not null OR
[P D] is not null OR
[P Rec] is not null OR
[P O] is not null OR
[P W] is not null
*/
Again, thanks for any insights.
~CB
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply