Adding Rows from another table as Columns in a Select

Transact-SQL
1

Can someone help point me in the right direction?

If I have a table, say

Table - Details
[ID]
[Name]
[Address]

Data example

ID=6,Name="Fred",Address="Fred Lives Here"

which is linked to another table via the [ID] column to the [DetailsID] column...

Table - CustomFields
[Key]
[DetailsID]
[FieldName]
[Value]

Data example

Key=71,DetailsID=6,FieldName="Custom 1", Value=17
Key=72,DetailsID=3,FieldName="Custom 1", Value=6
Key=73,DetailsID=6,FieldName="Custom 2", Value="Bill"

And I want to be able to output the Details table with each row from the CustomFields table as columns of the select...

Select * From [Details] Where ID = 6

would give...

[ID],[Name],[Address],[Custom 1],[Custom 2]
6, "Fred", "Fred Lives here",17,"Bill"

Appreciate all the help

Thanks

Phil

2

That sounds like a simple JOIN of the two tables like this:

SELECT
	c.[Key],
	c.[DetailsID],
	c.[FieldName],
	c.[Value],
	d.[Id],
	d.[Name],
	d.[Address]
FROM
	CustomFields c
	INNER JOIN Details d ON
		d.Id = c.DetailsId;
3

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 :frowning:

Hope that makes sense

Phil

4

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
5

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)

6

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

Phil

7

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

8

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