Procedure or T-SQL to build new table
-
Ok,
we have 2 tables in SQL.
and need to do this using procedure or straight SQL.
Anyone can help?
The only problem is - 2 top tables are changing all the time. Sothere could be more responses or more names in both tables.
Pretty much dynamic solution of transforming Column I guess to a row.
The final table needs to be outputted at the end in .asp (.xsl)
-
I absolutely do not understand what problem you are trying to solve.
The image you link to is not very helpful.
Can you more clearly state your problem, and perhaps post some DDL and what results you expect to get back.
-
Ok, Let's say I have 2 tables in SQL DB. For example:
And I need to build XML that will look like:
<NAME><RESPONSE1>blabla</RESPONSE1> <RESPONSE2>blabla</RESPONSE2> <RESPONSE3>blabla</RESPONSE3> </NAME>
From fields Name(1st table) and Response(2nd table). Then I'll build table myself. I just need to figure out how to get XML for those 2 dynamic tables. Cuz records in them change all the time.
Is this better? =)
-
Almost.
What is the relationship between table 1 and table 2?
The xml asside, would you say the value for FieldID 7227 is in Table B many times for different EventID's?
You want the names from Table A to act as you Element Names in the XML for the Responses in Table B?
The data snipits you show do not relate to each other.
Can you post your table schema, and some sample data so we don't have to guess at what your after.
-
Relationship is: Table 1 has Column "Name" - that's name for each field on Dynamic Form(already have written dynamic form upload) which can be textarea, and other inputs.
Now "Response" on Table 2 is what those input fields from Form have. So they are directly related to Table 1 "Name" and connected by "FieldID".
One "FieldId" has only one "Response" and one "Name" - nothing multiple.
About EventID - yes. Each EventID and UserID has multiple FieldIDs. I'm sure it's more clear after I explained on top what it is for =)
Here is how it would look in Internet Explorer at the end(User won't see FormID, FieldID and other of course =) but they will be there to relate to different Users.):
Oh, yeah, on example tables - FieldIDs were suppose to be identical as you can see from a final result. Sorry =) took wrong screenshot.
-
Image is a broken link.
I think I get the relationships
You want the recordset output as xml?
Since your table schema, (Or lack of schema) is structured this way it would probably be easier to output a recordset of fields and responses, and pivot the data in the applicaiton.
How do you want the xml to be sturecuted to be well formed?
what would the parent Node be? The xml you show above does not make sense.
-
Here is the image of a final result:
Sorry it didn't work last time.
People talk about pivot - but i have no clue how to do it. Can u help plz?
-
Okay you have way too much going on here, You have a table that contains fields for virtual tables, then you have another table that has values for those virtual fields. this is not the way RDBMS were meant to be used.
I would suggest you actually do some modeling on the entities your capturing data for. Then I would try a hundred other things before I came up with this solution. If this is all you can think of then I would suggest you call the data from the database as a recordset with the formID, eventID, FieldName, Response value etc. and Use the application to create the XML, or Pivot table.
Doing it from the database is not efficient, and not what sql server was meant to do.
That being said below will work. It uses dynamic sql so I suggest you read this.
http://www.sommarskog.se/dynamic_sql.html
Also read this about pivoting data.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_04j7.asp
I do not recommend this solution for a production application.
Next time please post create table, and insert data scripts so we don't have to guess what your question is.
Create Table Temp1 (FieldID int identity, FormID int, FName varchar(50))
-- Test Fields
Insert into Temp1 (FormID, Fname)
values (233, 'ContactName')
Insert into Temp1 (FormID, Fname)
values (233, 'EmailAddress')
Insert into Temp1 (FormID, Fname)
values (233, 'JobDescription')
Insert into Temp1 (FormID, Fname)
values (233, 'Structure')
Insert into Temp1 (FormID, Fname)
values (100, 'StreetNumber')
Insert into Temp1 (FormID, Fname)
values (100, 'StreetName')
Insert into Temp1 (FormID, Fname)
values (100, 'City')
Create table Temp1Response (pk int identity, FieldID int, EventID int, Response Varchar(100))
-- Responses
Insert into Temp1Response (FieldID, EventID, Response)
Values(1, 1, 'JoeSomebody')
Insert into Temp1Response (FieldID, EventID, Response)
Values(2, 1, 'jSomebody@somewhere.com')
Insert into Temp1Response (FieldID, EventID, Response)
Values(3, 1, 'Garbage Man')
Insert into Temp1Response (FieldID, EventID, Response)
Values(4, 1, 'Partner')
Insert into Temp1Response (FieldID, EventID, Response)
Values(5, 2, '100')
Insert into Temp1Response (FieldID, EventID, Response)
Values(6, 2, 'Elm Street')
Insert into Temp1Response (FieldID, EventID, Response)
Values(7, 2, 'Nowhere')
-- Simple Pivot of Fields
select min(case A.FieldID when 1 then FName end),
min(case A.FieldID when 2 then FName end),
min(case A.FieldID when 3 then FName end),
min(case A.FieldID when 4 then FName end)
from Temp1 A
join Temp1Response B on A.fieldId = b.FieldID
where formID = 233
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'ReturnResponseByEventRS'
AND type = 'P')
DROP PROCEDURE ReturnResponseByEventRS
GO
CREATE PROCEDURE ReturnResponseByEventRS
@FormID int
, @EventID int
AS
-- Stored procedure to return records Pivoted with FieldName as Column Header
DECLARE @SqlString nvarchar(4000)
-- Using Dynamic Sql Create a string to execute.
-- This statement creates the Pivot Table string that has the fields field name as an alias from the field table.
SELECT @SqlString = coalesce(@SqlString,'') + ' min(case B.FieldID when ' + ltrim(Str(A.FieldID)) + ' then Response end) as [' + FName + '],'
FROM Temp1 A
JOIN Temp1Response B on A.fieldId = b.FieldID
WHERE formID = @FormID
-- Remove Last comma
Set @SqlString = SUBSTRING(@SqlString,1, len(@SqlString) -1)
-- Put that pivot string into a select statement
SET @SqlString = 'SELECT ' + @SqlString + '
FROM Temp1 A
JOIN Temp1Response B on A.fieldId = b.FieldID
Where FormID = @FormID
and EventID = @EventID
'
-- select @SqlString, @SqlString2
-- Execute the string by the Form and event id passed in.
exec sp_executeSql @SqlString, N'@FormID int, @EventID int', @FormID, @EventID
Go
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'ReturnResponseByEventXML'
AND type = 'P')
DROP PROCEDURE ReturnResponseByEventXML
GO
CREATE PROCEDURE ReturnResponseByEventXML
@FormID int
, @EventID int
AS
-- Stored procedure to return XML with FieldName as ElementName
DECLARE @SqlString nvarchar(4000)
, @SqlString2 nvarchar(1000)
-- Using Dynamic Sql Create a string to execute.
-- This statement creates the Pivot Table string that has the fields field name as an alias from the field table.
SELECT @SqlString = coalesce(@SqlString,'') + ' min(case B.FieldID when ' + ltrim(Str(A.FieldID)) + ' then Response end) as [' + FName + '],'
FROM Temp1 A
JOIN Temp1Response B on A.fieldId = b.FieldID
WHERE formID = @FormID
-- Create a second string that Creates the information for Explicit xml Call
Select @SqlString2 = coalesce(@SqlString2,'') + fname + ' [Response!1!' + Fname+'!Element],'
FROM Temp1 A
JOIN Temp1Response B on A.fieldId = b.FieldID
WHERE formID = @FormID
-- Remove Last comma
Set @SqlString = SUBSTRING(@SqlString,1, len(@SqlString) -1)
Set @SqlString2 = SUBSTRING(@SqlString2,1, len(@SqlString2) -1)
-- Put strings into one string with select statement.
SET @SqlString = 'Select 1 as Tag,
Null as Parent,
' + @SqlString2 + '
from (SELECT ' + @SqlString + '
FROM Temp1 A
JOIN Temp1Response B on A.fieldId = b.FieldID
Where FormID = @FormID
and EventID = @EventID) Dt
For XML Explicit'
-- select @SqlString, @SqlString2
-- Execute the string by the Form and event id passed in.
exec sp_executeSql @SqlString, N'@FormID int, @EventID int', @FormID, @EventID
Go
exec ReturnResponseByEventXML @FormID=233, @EventID = 1
-- Result
-- <Response><ContactName>JoeSomebody</ContactName><EmailAddress>jSomebody@somewhere.com</EmailAddress><JobDescription>Garbage Man</JobDescription><Structure>Partner</Structure></Response>
-- Same Proc Different Form and event id.
exec ReturnResponseByEventXML @FormID=100, @EventID = 2
-- Result
-- <Response><StreetNumber>100</StreetNumber><StreetName>Elm Street</StreetName><City>Nowhere</City></Response>
exec ReturnResponseByEventRS @FormID=233, @EventID = 1
-- Result
-- ContactName,EmailAddress,JobDescription,Structure
-- JoeSomebody,jSomebody@somewhere.com,Garbage Man,Partner
-- Same Proc Different Form and event id.
exec ReturnResponseByEventRS @FormID=100, @EventID = 2
-- Result
-- StreetNumber,StreetName,City
-- 100,Elm Street,Nowhere
Drop Table Temp1
Drop Table Temp1Response
DROP PROCEDURE ReturnResponseByEventXML
DROP PROCEDURE ReturnResponseByEventRS
-
Thank you so much for responding.
This is great help.
I understand everything, except how can I make it work with dynamic sql tables? I mean 2 original tables in SQL are changing all the time. Number of responses will be increasing with time. I can never know what will they be later on. The same with Field Names.
I mean did I miss something or this will work only for what I had in the example table?
Create Table Temp1 (FieldID int identity, FormID int, FName varchar(50))
-- Test Fields
Insert into Temp1 (FormID, Fname)
values (233, 'ContactName')
Insert into Temp1 (FormID, Fname)
values (233, 'EmailAddress')
Insert into Temp1 (FormID, Fname)
values (233, 'JobDescription')
Insert into Temp1 (FormID, Fname)
values (233, 'Structure')
Insert into Temp1 (FormID, Fname)
values (100, 'StreetNumber')
Insert into Temp1 (FormID, Fname)
values (100, 'StreetName')
Insert into Temp1 (FormID, Fname)
values (100, 'City')
Create table Temp1Response (pk int identity, FieldID int, EventID int, Response Varchar(100))
-- Responses
Insert into Temp1Response (FieldID, EventID, Response)
Values(1, 1, 'JoeSomebody')
Insert into Temp1Response (FieldID, EventID, Response)
Values(2, 1, 'jSomebody@somewhere.com')
Insert into Temp1Response (FieldID, EventID, Response)
Values(3, 1, 'Garbage Man')
Insert into Temp1Response (FieldID, EventID, Response)
Values(4, 1, 'Partner')
Insert into Temp1Response (FieldID, EventID, Response)
Values(5, 2, '100')
Insert into Temp1Response (FieldID, EventID, Response)
Values(6, 2, 'Elm Street')
Insert into Temp1Response (FieldID, EventID, Response)
Values(7, 2, 'Nowhere')
-
I don't know what you mean by dynamic tables?
This should work with the tables you use.
-- When your creating the Crosstab query string, Replace your tables here
SELECT @SqlString = coalesce(@SqlString,'') + ' min(case B.FieldID when ' + ltrim(Str(A.FieldID)) + ' then Response end) as [' + FName + '],'
FROM Temp1 A
JOIN Temp1Response B on A.fieldId = b.FieldID
WHERE formID = @FormID
(Also replace Temp1, and Temp1Response with your table names in the dynamic sql string declaration.)
Are you saying you want to use this same procedure with different tables in your database?, Or different virtual tables Stored in the same table you indicated on your request?
Notice on the example I have 2 different FormID's, and when you run the same stored procedure with the form id of 100 different results are coming up?
I am assuming formID is the identifier that indicates a different table.
-
I meant dynamic like - all the data changes all the time. Records, amount of fields and etc.
Well, that's what I did with it to get all the proper data in the beginning. And I changed table names everywhere.
SELECT FieldID, FormID, Name INTO FormFields from dbo.FormFields
SELECT UserID, FieldID, Response INTO FormFieldResponse from dbo.FormFieldResponse
-- Pivot the fields...
select min(A.FieldID)
from FormFields A
join FormFieldResponse B on A.fieldId = B.FieldID
IF EXISTS (SELECT Name
FROM dbo.FormFields
WHERE Name = N'ReturnResponseByEventRS'
AND type = 'P')
DROP PROCEDURE ReturnResponseByEventRS
GO
CREATE PROCEDURE ReturnResponseByEventRS
@FormID int
, @EventID int
AS
DECLARE @SqlString nvarchar(4000)
-- Ispolzuem Dynamic Sql chtobi sozdat stroku dlya execute.
-- Eto sozdast Pivot Table stroku gde nazvanie fielda eto alias iz field table.
SELECT @SqlString = coalesce(@SqlString,'') + ' min(case B.FieldID when ' + ltrim(Str(A.FieldID)) + ' then Response end) as [' + Name + '],'
FROM FormFields A
JOIN FormFieldResponse B on A.fieldId = b.FieldID
WHERE formID = @FormID
-- Ubrat poslenduu zapyatuu
Set @SqlString = SUBSTRING(@SqlString,1, len(@SqlString) -1)
-- Vozmem pivot stroku v select statement
SET @SqlString = 'SELECT ' + @SqlString + '
FROM FormFields A
JOIN FormFieldResponse B on A.fieldId = b.FieldID
Where FormID = @FormID
and EventID = @EventID
'
-- Vibrat @SqlString, @SqlString2
-- Zapustit po Form and event id.
exec sp_executeSql @SqlString, N'@FormID int, @EventID int', @FormID, @EventID
Go
IF EXISTS (SELECT Name
FROM dbo.FormFields
WHERE name = N'ReturnResponseByEventXML'
AND type = 'P')
DROP PROCEDURE ReturnResponseByEventXML
GO
CREATE PROCEDURE ReturnResponseByEventXML
@FormID int
, @EventID int
AS
-- Stored procedure chtobi otobrazit XML s FieldName kak ElementName
DECLARE @SqlString nvarchar(4000)
, @SqlString2 nvarchar(1000)
-- Uzaem Dynamic Sql chtobi sozdat stroku dlya execute.
-- Sozdaem Pivot Table stroku that has the fields field name as an alias from the field table.
SELECT @SqlString = coalesce(@SqlString,'') + ' min(case B.FieldID when ' + ltrim(Str(A.FieldID)) + ' then Response end) as [' + Name + '],'
FROM FormFields A
JOIN FormFieldResponse B on A.fieldId = b.FieldID
WHERE formID = @FormID
-- Sozdaem vtoruu stroku kotoraya sozdast Explicit xml Call
Select @SqlString2 = coalesce(@SqlString2,'') + Name + ' [Response!1!' + Name +'!Element],'
FROM FormFields A
JOIN FormFieldResponse B on A.fieldId = b.FieldID
WHERE formID = @FormID
-- Uberaem poslednuu zapyatuu snova
Set @SqlString = SUBSTRING(@SqlString,1, len(@SqlString) -1)
Set @SqlString2 = SUBSTRING(@SqlString2,1, len(@SqlString2) -1)
-- Berem obe stroki i logim ih v odnu select statement.
SET @SqlString = 'Select 1 as Tag,
Null as Parent,
' + @SqlString2 + '
from (SELECT ' + @SqlString + '
FROM FormFields A
JOIN FormFieldResponse B on A.fieldId = b.FieldID
Where FormID = @FormID
and EventID = @EventID) Dt
For XML Explicit'
exec sp_executeSql @SqlString, N'@FormID int, @EventID int', @FormID, @EventID
Go
exec ReturnResponseByEventXML @FormID = 233, @EventID
exec ReturnResponseByEventXML @FormID, @EventID
exec ReturnResponseByEventRS @FormID = 233, @EventID
exec ReturnResponseByEventRS @FormID, @EventID
Drop Table FormFields
Drop Table FormFieldResponse
DROP PROCEDURE ReturnResponseByEventXML
DROP PROCEDURE ReturnResponseByEventRS
The problem is when I try to run it - I get tht EventID and FormID are not declared. I think I need to make a loop(comma-separated string with split afterwords) so that procedures get all the values from the table.
-
Goober, You have syntactical errors when trying to execute the stored procedure.
ReturnResponseByEventXML requires a formID, and an Event ID, you have provided 233 as the Formid, but what is the event ID?
exec ReturnResponseByEventXML @FormID = 233, @EventID =?
Here what is the formID, and EventID?
exec ReturnResponseByEventXML @FormID = ?, @EventID = ?
The data is returned dynamically, if you add more fields to formID 233, they will automaticaly be added when you rerun the stored procedure as long as there are responses for that field. If you add more data to the data table it will show up. when the data exits. (if the data does not exist in the response table the field will not show up when the procedure is called)
If you want to return records by formID, and not Event ID run this.
IF EXISTS (SELECT Name
FROM dbo.FormFields
WHERE Name = N'ReturnResponseByFormXML'
AND type = 'P')
DROP PROCEDURE ReturnResponseByFormXML
GO
CREATE PROCEDURE ReturnResponseByFormXML
@FormID int
AS
-- Stored procedure chtobi otobrazit XML s FieldName kak ElementName
DECLARE @SqlString nvarchar(4000)
, @SqlString2 nvarchar(1000)
-- Uzaem Dynamic Sql chtobi sozdat stroku dlya execute.
-- Sozdaem Pivot Table stroku that has the fields field name as an alias from the field table.
SELECT @SqlString = coalesce(@SqlString,'') + ' min(case B.FieldID when ' + ltrim(Str(A.FieldID)) + ' then Response end) as [' + Name + '],'
FROM FormFields A
JOIN FormFieldResponse B on A.fieldId = b.FieldID
WHERE formID = @FormID
-- Sozdaem vtoruu stroku kotoraya sozdast Explicit xml Call
Select @SqlString2 = coalesce(@SqlString2,'') + Name + ' [Response!1!' + Name +'!Element],'
FROM FormFields A
JOIN FormFieldResponse B on A.fieldId = b.FieldID
WHERE formID = @FormID
-- Uberaem poslednuu zapyatuu snova
Set @SqlString = SUBSTRING(@SqlString,1, len(@SqlString) -1)
Set @SqlString2 = SUBSTRING(@SqlString2,1, len(@SqlString2) -1)
-- Berem obe stroki i logim ih v odnu select statement.
SET @SqlString = 'Select 1 as Tag,
Null as Parent,
' + @SqlString2 + '
from (SELECT ' + @SqlString + '
FROM FormFields A
JOIN FormFieldResponse B on A.fieldId = b.FieldID
Where FormID = @FormID) Dt
For XML Explicit'
exec sp_executeSql @SqlString, N'@FormID int', @FormID
Go
-- Pass the formID for the records you want back, you do not need to do any looping.
exec ReturnResponseByEventXML @FormID = 233
This is what the sql acuall looks like if you wrote it manually. Notice No Looping
SELECT min(case B.FieldID when 1 then Response end) as [ContactName], min(case B.FieldID when 2 then Response end) as [EmailAddress], min(case B.FieldID when 3 then Response end) as [JobDescription], min(case B.FieldID when 4 then Response end) as [Structure]
FROM Temp1 A
JOIN Temp1Response B on A.fieldId = b.FieldID
Where FormID = @233
-
Ok,
I understand. Sorry I paste code with missing parts... that's not the point though.
I need to output table with all FormIDs - we have over 300 of them. I just didn't present it here. And they keep growing - that's what the problem is. That's what I meant by dynamic.
-
I don't understand what you mean. The code I gave you allows you to return data by formID, You want to return data across more than 1 formid?
Please Post some data, and a clear example of what you want returned
-
Yes, exactly, I want to return data across all FormIDs.
Thanks so much for your help man. I know it's a lot.
Viewing 15 posts - 1 through 15 (of 69 total)
You must be logged in to reply to this topic. Login to reply