converting columns to rows
-
I have a table (tblLog) where I capture the field names and the field values at modification or deletion. the table looks like this:
userID TableName FieldName FieldValue Date
3 tblCommon FirstName John 7/8/2005
3 tblCommon LastName Doe 7/8/2005
3 tblCommon Initial B 7/8/2005
3 tblCommon DOB 1/1/1981 7/8/2005
I want it to look like:
UserID FirstName LastName Initial DOB Date
3 John Doe B 1/1/1981 7/8/2005
Any help will be greatly appriciated.
-
select UserID
, Coalsece(Min(case when FieldName = FirstName then FieldValue end ),'') As FirstName
, Coalsece(Min(case when FieldName = LastName then FieldValue end ),'') As LastName
, Coalsece(Min(case when FieldName = Initial then FieldValue end ),'') As Initial
, Coalsece(Min(case when FieldName = DOB then FieldValue end ),'') As DOB
, Date
from
tblLog
group by UserID , Date
order by UserID ,Date
* Noel -
I am getting :
Server: Msg 195, Level 15, State 10, Line 2
'Coalsece' is not a recognized function name.
any thoughts?
-
sorry I didn't tested that I have no sql on hand now but it was just a typo
coalesce is the right spelling
-- I used Copy and Paste therefore multiple errors
* Noel -
Thank you very much Noel. it worked perfectly!
-
I'm trying to do the exact same thing, but trying to figure out a way to make the "FieldName FieldValue Date" dynamic.
In other words, someone might add a new FieldName to the mix and I want to catch them all, not "program" a set number of values in a case statement.
Any ideas? Thanks!
-
There is no way to do it "Dynamically" the values of the fields need to be known in advance.
You can perform this using "Dynamic Sql" but it will be a little challenging.
http://www.sommarskog.se/dynamic_sql.html
I did something like this that helped another Member:
Create Table UserInfo (UserID int, FirstName varchar(50), LastName varchar(50), Organization varchar(50))
Create Table Forms (FormID int, Title varchar(50))
Create Table Events (EventID int, EventName varchar(50))
Insert into Forms(FormID, Title)
Values (233, 'TitleForm')
Insert into Forms(FormID, Title)
Values (100, 'Form2Title')
Insert into Events(EventID, EventName)
Values (1, 'Event1')
Insert into Events(EventID, EventName)
Values (2, 'Event2')
Insert into UserInfo (FirstName, LastName, Organization)
select 'Joe', 'Somebody', 'Party House'
union
select 'Fred', 'Flinstone', 'Animal House'
Create Table FormFields (FieldID int identity, FormID int, Name varchar(50))
-- Test Fields
Insert into FormFields (FormID, Name)
values (233, 'ContactName')
Insert into FormFields (FormID, Name)
values (233, 'EmailAddress')
Insert into FormFields (FormID, Name)
values (233, 'JobDescription')
Insert into FormFields (FormID, Name)
values (233, 'Structure')
Insert into FormFields (FormID, Name)
values (100, 'StreetNumber')
Insert into FormFields (FormID, Name)
values (100, 'StreetName')
Insert into FormFields (FormID, Name)
values (100, 'City')
Insert into FormFields (FormID, Name)
values (233, 'EmailAddress')
Create table FormFieldResponse (responseID int identity, FieldID int, UserID int, EventID int, Response Varchar(100))
-- Responses
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(1, 1, 1, 'JoeSomebody')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(2, 1, 1, 'jSomebody@somewhere.com')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(3, 1, 1, 'Garbage Man')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(4, 1, 1, 'Partner')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(5, 2, 1, '100')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(6, 2, 1, 'Elm Street')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(7, 2, 1, 'Nowhere')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(8, 2, 1, 'Someone@Somedomain.com')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(1, 1, 2, 'Fred Flinstone')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(2, 1, 2, 'jFlinstone@somewhere.com')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(3, 1, 2, 'Plumber')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(4, 1, 2, 'Partner')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(5, 2, 2, '500')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(6, 2, 2, 'Oak Street')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(7, 2, 2, 'Elsewhere')
Insert into FormFieldResponse (FieldID, EventID, UserID, Response)
Values(8, 2, 2, 'Anyone@Somedomain.com')
GO
Create Proc Procedure1 @eventID int
as
DECLARE @SqlString varchar(8000)
DECLARE @Orderby nvarchar(250)
DECLARE @SqlString2 nvarchar(4000)
DECLARE @FieldList1 varchar(8000)
-- Set FieldList1
set @Sqlstring2 = '
SELECT @sql = coalesce(@Sql,'''') + '' ISNULL(min(case b.Name when '''''' + DT.Name + '''''' then Response end),0) as ['' + DT.Name + ''],''
FROM (select distinct REPLACE(b.name, '' '', ''_'') as Name
from formfieldResponse a
join formfields b on a.FieldID = b.fieldID
WHERE a.EventID = @EventID ) DT '
exec sp_executeSql @SqlString2, N'@EventID int, @sql varchar(8000) OUTPUT ', @EventID, @FieldList1 OUTPUT
set @FieldList1 = left(@FieldList1, len(@FieldList1) -1)
set @sqlstring = ' select ' + @FieldList1 + '
from formfieldResponse a
join formfields b on a.FieldID = b.fieldID
where a.EventID = ' + ltrim(str(@EventID))
-- print @sqlstring
exec (@sqlstring)
GO
exec Procedure1 @eventID = 2
exec Procedure1 @eventID = 1
set nocount on
Drop table FormFields
Drop table FormFieldResponse
Drop table UserInfo
Drop table Forms
Drop table Events
Drop Proc Procedure1
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply