Wont that give me a output row for each Custom Field, so we would have Fred's record output twice?
I am trying to get each matching row from CustomFields to be output as columns for the output record for Fred (i.e., only one line for Fred)
So it would need a select something like this simulated select....
SELECT
d.[Id],
d.[Name],
d.[Address]
c.[Value] as c.[FieldName] where C.FieldID = d.ID,
c.[Value] as c.[FieldName] where C.FieldID = d.ID,
It is sounding overly complicated, so maybe I need to think the design. The problem is I need to be able to do this for a report output for a client and I am stuck with the custom fields linked to the details table in this way
try this, but I'm not sure if is going to work for you
create table #detail
(
Id int,
Name varchar(50),
Address varchar(100)
);
insert into #detail
values
(6, 'Fred','Fred lives here');
create table #CustomField
(
[Key] int,
DetailsID int,
FieldName varchar(50),
Value varchar(10)
);
insert into #CustomField
values
(71,6,'Custom 1','17'),
(72,3,'Custom 1','6'),
(73,6,'Custom 2','Bill')
;
select * from #CustomField
with Mycte
as
(
select d.Id, d.Name, d.Address, pvt.[Custom 1],pvt.[Custom 2] from #detail as d
inner join(
select [Key],DetailsID, [Custom 1], [Custom 2] from #CustomField
pivot(max(value) for FieldName in([Custom 1], [Custom 2]) )as p
) as pvt
on d.Id =pvt.DetailsID
)
select ct1.Id, ct1.Name, ct1.Address,
ct1.[Custom 1], ct2.[Custom 2] from myCTE as ct1
inner join myCTE as ct2
on ct1.Id=ct2.Id and ct1.[Custom 1] is not null and ct2.[Custom 2] is not null
Do you need individual columns for the CustomFields (rows), or would a delimited list do? e.g. "17, Bill" or "Custom 1=17, Custom 2=Bill" (the "Custom 1" labels could be whatever is appropriate)
I just mention this because the solution to getting such a "delimited list" might be more lightweight than having to use PIVOT and may be more workable from the Application's perspective (that receives & processes the data) in particular if new FieldName values can be added to the CustomerFields table (e.g. by Operators rather than Developers)
Hi Kristen - I think you are right; the overhead and complexity to handle columns exceeds the benefit. I will see if we can use a delimited list as you say - this will still allow them to view the fields and search, so should be accepted
Thanks Scarela - this does do what I need, but as mentioned in another thread I think the complexity is outweighing the benefit (there could be any number of custom fields!), so I am having a rethink on the design
Just in case it helps, here's my "template code" for concatenating a comma-delimited list from a child-table
SELECT H.HeaderCol1
, H.HeaderCol2
, [MyValueList] = STUFF(
(
--TODO Use COALESCE(C.ColToList, '') to preserve NULL values
TODO Choose ONE of these SELECT statements!
SELECT ',' + C.StringColToList
SELECT ',' + CONVERT(varchar(20), C.NumericColToList)
FROM ChildTable AS C
WHERE C.MyKey = H.MyKey
--TODO Set appropraite column(s) to Order the dfelimited-list. Add PKey if necessary to make it unique (i.e. repeatable)
ORDER BY C.C_ID
FOR XML PATH(''), TYPE
--TODO NOTE: "'value" is case sensitive!!
).value('.', 'varchar(max)')
--TODO Use ", 1, 2, '')" if the delimiter includes a space e.g. ", "
, 1, 1, '')
FROM HeaderTable AS H