help with procedure
-
ok, imma give all the data so it's easier to understand. What's on the left is 4 tables in DB and on the right the result I need to get. I will be passing FieldID and FormID into the procedure. Sorry for all this crazy data =) Oh yeah, data in DB is dynamic and changes daily.
FieldID FormID ReportName Item John Supplier Beth Supplier 290 86 Account 1 >>> Organization LLC 1 LLC 2 291 86 Account 11 >>> Event Name Friday Test 0 292 86 Account 2 >>> Form Name TestForm1 0 293 86 Account 22 >>> Account 1 Account A 0 294 86 Account 3 >>> Account 11 This is info… 0 295 86 Account 33 >>> Account 2 Account B 0 296 89 Account 58 >>> Account 22 This is info… 6700 >>> Account 3 Account C 0 EventID FieldID UserID Response >>> Form Name This is info… 0 347 925 405 8555 >>> Account 33 Response 295 0 160 296 405 Beth Response 1 >>> Event Name Friday 2 0 159 293 405 6700 >>> Form Name TestForm3 0 159 295 404 Response 295 >>> Account 58 Response 58 Beth Response 1 159 290 404 Account A >>> 159 291 404 This is information about Account A. >>> 159 292 404 Account B >>> 159 293 404 This is information about Account B. >>> 159 294 404 Account C >>> 160 296 404 Response 58 >>> >>> UserID LastName FirstName Organization >>> 404 Supplier John LLC 1 >>> 405 Supplier Beth LLC 2 >>> 406 Supplier Carl LLC 3 >>> 407 Supplier Dillion LLC 4 >>> >>> EventID EventName 159 Friday Test 160 Friday 2 161 Friday 3 162 May Test FormID FormName 86 TestForm1 87 TestForm2 89 TestForm3 Hope someone can help
-
no one knows?
-
Maybe you should try restating your problem with more details... there's no obvious solution here.
-
I thought tables show everything in pretty easy way.
What other details should I provide?
-
Dumb @$$.. I thaught you had 7 tables instead of 5...
can you post the ddl and dml statements to create the tables and insert this data?... or you can also search for pivot tables on this site and you'll find the solution.
-
I was trying to find some solution, but couldn't. COuld you please help me do this?
here is statements as you asked:
create table zTempTable1 (FieldID int, FormID int, ReportName varchar(128))
create table zTempTable2 (EventID int, FieldID int, UserID int, Response varchar(1000))
create table zTempTable3 (UserID int, LastName varchar(100), FirstName varchar(100), Organization varchar(128))
create table zTempTable4 (EventID int, EventName varchar(50))
create table zTempTable5 (FormID int, FormName varchar(128))
insert into zTempTable1(FieldID, FormID, ReportName)
select 290,86,'Account 1' union all select 291,86,'Account 11' union all select 292,86,'Account 2' union all select 293,86,'Account 22' union all select 294,86,'Account 3' union all select 295,86,'Account 33' union all select 296,89,'Account 58'
insert into zTempTable2(EventID, FieldID, UserID, Response)
select 347,925,405,'8555' union all select 160,296,405,'Beth Response 1' union all select 159,293,405,'6700' union all select 159,295,404,'Response 295' union all select 159,290,404,'Account A' union all select 159,291,404,'This is information A' union all select 159,292,404,'Account B' union all select 159,293,404,'This is information B' union all select 159,294,404,'Account C' union all select 160,296,404,'Response 58'
insert into zTempTable3(UserID, LastName, FirstName, Organization)
select 404,'Supplier','John','LLC 1' union all select 405,'Supplier','Beth','LLC 2' union all select 406,'Supplier','Carl','LLC 3' union all select 407,'Supplier','Dillion','LLC 4'
insert into zTempTable4(EventID, EventName)
select 159,'Friday Test' union all select 160,'Friday 2' union all select 161,'Friday 3' union all select 162,'May Test'
insert into zTempTable5(FormID, FormName)
select 86,'TestForm1' union all select 87,'TestForm2' union all select 89,'TestForm3'
-
Some of the desired output is confusing, but here is a start. I have changed the table names from the cryptic zTempTable to more meaninful names, add primary keys and foreign key constraints.
Good Luck with the rest.
create schema authorization dbo
create table Events
( EventID int NOT NULL
, EventName varchar(50) NOT NULL
, constraint Events_P primary key (EventId)
)
create table Forms
( FormID int NOT NULL
, FormName varchar(128) NOT NULL
, constraint Forms_P primary key (FormId)
)
create table FormFields
( FieldID int NOT NULL
, FormID int NOT NULL
, ReportName varchar(128) NOT NULL
, constraint FormFields_P primary key (FieldId)
, constraint Forms_F_FormFields foreign key (FormId) references Forms
)
create table Persons
( UserID int NOT NULL
, LastName varchar(100) NOT NULL
, FirstName varchar(100) NOT NULL
, Organization varchar(128) NOT NULL
, constraint Persons_P primary key (UserID )
)
create table Responses
( EventID int NOT NULL
, FieldID int NOT NULL
, UserID int NOT NULL
, Response varchar(1000) NOT NULL
, constraint Responses_P primary key (EventID, FieldId, UserID )
, constraint Events_F_Responses foreign key (EventID) references Events
)
go
insert into Persons(UserID, LastName, FirstName, Organization)
select 404,'Supplier','John','LLC 1' union all
select 405,'Supplier','Beth','LLC 2' union all
select 406,'Supplier','Carl','LLC 3' union all
select 407,'Supplier','Dillion','LLC 4'
go
insert into Events(EventID, EventName)
select 159,'Friday Test' union all
select 160,'Friday 2' union all
select 161,'Friday 3' union all
select 162,'May Test'
go
insert into Forms(FormID, FormName)
select 86,'TestForm1' union all
select 87,'TestForm2' union all
select 89,'TestForm3'
go
insert into FormFields(FieldID, FormID, ReportName)
select 290,86,'Account 1' union all
select 291,86,'Account 11' union all
select 292,86,'Account 2' union all
select 293,86,'Account 22' union all
select 294,86,'Account 3' union all
select 295,86,'Account 33' union all
select 296,89,'Account 58'
go
insert into Responses(EventID, FieldID, UserID, Response)
-- select 347,925,405,'8555' union all
select 160,296,405,'Beth Response 1' union all
select 159,293,405,'6700' union all
select 159,295,404,'Response 295' union all
select 159,290,404,'Account A' union all
select 159,291,404,'This is information A' union all
select 159,292,404,'Account B' union all
select 159,293,404,'This is information B' union all
select 159,294,404,'Account C' union all
select 160,296,404,'Response 58'
go
select 1 as RowNumber
, 'Item' as RDescr
, MAX( CASE WHEN Persons.UserId = 404 then Persons.FirstName else null end )
, MAX( CASE WHEN Persons.UserId = 405 then Persons.FirstName else null end )
FROM Persons
where EXISTS
(select 1
from Responses
where Responses.UserId = Persons.UserId
 
union all
select 2 as RowNumber
, 'Organization' as RDescr
, MAX( CASE WHEN Persons.UserId = 404 then Persons.Organization else null end )
, MAX( CASE WHEN Persons.UserId = 405 then Persons.Organization else null end )
FROM Persons
where EXISTS
(select 1
from Responses
where Responses.UserId = Persons.UserId
 
union all
select 3 as RowNumber
, 'EventName' as RDescr
, MAX( CASE WHEN Responses.UserId = 404 then Events.EventName else null end )
, MAX( CASE WHEN Responses.UserId = 405 then Events.EventName else null end )
FROM Responses
join Events
on Events.EventId = Responses.EventId
The result of the SQL is:
1 Item John Beth
2 Organization LLC 1 LLC 2
3 EventName Friday Test Friday Test
SQL = Scarcely Qualifies as a Language
-
Yeah that would work except.... thanks for replying though.
As I said - data in DB is dynamic, procedure should not rely on UserID.
Procedure will only receive 2 parameters - set of EventIDs and FieldIDs. So we can't do CASE WHEN Persons.UserId = 404
-
You want all the users to be presented vertically?
-
Yeah, only users associated with those specific EventIDs and FieldIDs we are passing in should show up.
-
You'd need dynamic sql to create the pivot script... ask Noeld for this, he's much more expert than I am for that type of query, but he's at pass right now so it might take a while.
Also I'm wondering why you cannot present this group by users like we usually do??
-
Are you looking at throwing 0 - n EventIDs and 0 - n FieldIDs at this one query and returning the data the same as in Carls last post e.g:
1 Item John Beth
2 Organization LLC 1 LLC 2
3 EventName Friday Test Friday Test
Any chance someone can do the sql, similar to Carls earlier example but based around a single EventId and a single FieldId.
That would take me ages.... 
I'm also assuming there must be at least one of each - EventId and FieldId - to work with.
Steve
We need men who can dream of things that never were.
-
Hi,
You want all the users to be presented vertically? Yeah, only users associated with those specific EventIDs and FieldIDs we are passing in should show up.
Just because you only want users with the specific EventIDs and FieldIDs that are passed in, showing up - doesn't necessarily mean you need the data pesented vertically. What reason is there for presenting the data vertically?
Told you that bit would take me too long Here is a rough and ready solution that brings back the same info as in Carls earlier post - just horizontally instead of vertically.....
Add to / remove from the SQL to suit your own needs.
Two parts to this answer ('cos it was as easy to use List to Table functionality from an existing sproc
). First section SQL - Copy into QA:
------------------------------------------------------------------------
--Works on the Tables and Data created from Carls earlier posting:-
-- Events , Forms , FormFields , Persons , Responses
DECLARE @InputEventIDs VARCHAR(4000)--Delimited between each EventID by |
DECLARE @InputFieldIDs VARCHAR(4000)--Delimited between each FieldID by |
DECLARE @ID INT
DECLARE @MaxID INT
DECLARE @EventIDs VARCHAR(4000)
DECLARE @FieldIDs VARCHAR(4000)
DECLARE @SQLSelectFrom VARCHAR(4000)
DECLARE @SQLWhere VARCHAR(4000)
-----------------------------------------
--Test Data - bin this section when sproc created
SET @InputEventIDs = '159|160'
SET @InputFieldIDs = '290|291|292'
--Use @InputEventIDs and @InputFieldIDs as Input Variable
-----------------------------------------
--Create Temporary tables to hold the Event and Field ID's
CREATE TABLE #tblEventIds ([PK] [int] IDENTITY (1, 1) NOT NULL, colEventIds INT NOT NULL)
CREATE TABLE #tblFieldIds ([PK] [int] IDENTITY (1, 1) NOT NULL, colFieldIds INT NOT NULL)
--Use the List To Table Sproc to break down the list of ID's into the relevant tables
EXEC p_ListToTable @InputEventIDs, '|', '#tblEventIds', 'colEventIds'
EXEC p_ListToTable @InputFieldIDs, '|', '#tblFieldIds', 'colFieldIds'
--The Select statement doesn't need to be dynamic, so we can hard code most of it into the first variable
SET @SQLSelectFrom = 'SELECT Events.EventID, Responses.FieldID, Persons.FirstName, Persons.Organization,
Events.EventName
FROM Events INNER JOIN
Responses ON Events.EventID = Responses.EventID INNER JOIN
FormFields ON Responses.FieldID = FormFields.FieldID INNER JOIN
Forms ON FormFields.FormID = Forms.FormID INNER JOIN
Persons ON Responses.UserID = Persons.UserID'
--Code the first part of the WHERE clause
SET @SQLWhere = ' WHERE (Events.EventID IN ('
--Get a start and end point for the EventID loop
SET @ID = (SELECT MIN(colEventIds) FROM #tblEventIds)
SET @MaxID = (SELECT MAX(colEventIds) FROM #tblEventIds)
--Run from the smallest EventID to the largest (inclusive)
While @ID <= @MaxID
BEGIN
--Build up the WHERE clause with each EventID
SET @SQLWhere = @SQLWhere + '''' + CAST(@ID AS VARCHAR(4)) + ''''
SET @ID = (SELECT MIN(colEventIds) FROM #tblEventIds WHERE colEventIds > @ID)
--Check if it's the last one. If not - add a comma between values for the IN comparison
IF @ID <= @MaxID
BEGIN
SET @SQLWhere = @SQLWhere + ', '
END
END
--Set the WHERE clause up for the FieldID's
SET @SQLWhere = @SQLWhere + ')) AND (Responses.FieldID IN ('
--Get a start and end point for the FieldID loop
SET @ID = (SELECT MIN(colFieldIds) FROM #tblFieldIds)
SET @MaxID = (SELECT MAX(colFieldIds) FROM #tblFieldIds)
--Run from the smallest FieldID to the largest (inclusive)
While @ID <= @MaxID
BEGIN
--Build up the rest of the WHERE clause with each FieldID
SET @SQLWhere = @SQLWhere + '''' + CAST(@ID AS VARCHAR(4)) + ''''
SET @ID = (SELECT MIN(colFieldIds) FROM #tblFieldIds WHERE colFieldIds > @ID)
--Check if it's the last one. If not - add a comma between values for the IN comparison
IF @ID <= @MaxID
BEGIN
SET @SQLWhere = @SQLWhere + ', '
END
END
--Finish off the WHERE clause
SET @SQLWhere = @SQLWhere + '))'
--Bin the Temporary tables
DROP TABLE #tblEventIds
DROP TABLE #tblFieldIds
--And Finally - Execute the satement
EXEC (@SQLSelectFrom + @SQLWhere)
------------------------------------------------------------------------
Second bit - Copy and create this sproc in the database.
------------------------------------------------------------------------
/*###########################################################################################
PURPOSE
Takes in a list of values in a string, a delimiting character, a table name and a column name and seperates the values
based around the delimiting character
PARAMETERS
@vcList - List of values, delimited by @vcDelimiter
@vcDelimiter - Delimiting character
@TableName - Name of Table to pass seperated values into
@ColumnName - Names of Column to pass seperated values into
NOTES
############################################################################################*/
CREATE PROCEDURE [p_ListToTable]
@vcList VARCHAR(8000),
@vcDelimiter VARCHAR(8000),
@TableName SYSNAME,
@ColumnName SYSNAME
AS
SET NOCOUNT ON
DECLARE @iPosStart INT,
@iPosEnd INT,
@iLenDelim INT,
@iExit INT,
@vcStr varchar(8000),
@vcSql varchar(8000)
SET @iPosStart = 1
SET @iPosEnd = 1
SET @iLenDelim = LEN(@vcDelimiter)
SET @vcSql = 'INSERT ' + @TableName + ' (' + @ColumnName + ') VALUES ('''
SET @iExit = 0
WHILE @iExit = 0
BEGIN
SET @iPosEnd = CHARINDEX(@vcDelimiter, @vcList, @iPosStart)
IF @iPosEnd <= 0
BEGIN
SET @iPosEnd = LEN(@vcList) + 1
SET @iExit = 1
END
SET @vcStr = SUBSTRING(@vcList, @iPosStart, @iPosEnd - @iPosStart)
EXEC(@vcSql + @vcStr + ''')')
SET @iPosStart = @iPosEnd + @iLenDelim
END
RETURN 0
GO
---------------------------------------------------------------------------
I always prefered to be Horizontal instead of Vertical anyway..
Still can't believe Remi advocated the use of Dynamic SQL, you are honoured....
Have a good weekend all - above all
Have Fun
Steve
We need men who can dream of things that never were.
-
I said it would be better to present it horizontally as we always do, and that to make it happen vertically you'd have to use dynamic sql... please also note that I not spent another minute on this problem
.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply