My table has a name column with some records. In addition I would like a new column where each name has a relation to all existing names. Frank is related to all names in the table, Heather is related to all names in the table and so forth….
Even without knowing what your end goal is, the proposed design - creating columns and column names based on data in the table - is not a good one. If you can explain what you are trying to accomplish, many of the experts on this forum should be able to offer suggestions for a robust design pattern.
1 Like
As JamesK noted, you should not do this by adding a physical column(s).
A quick answer to the initial q – with the q perhaps not fully fleshed out yet – would be to do a CROSS JOIN of names:
SELECT t1.name AS name1, t2.name AS name2
FROM dbo.table_with_names t1
CROSS JOIN dbo.table_with_names t2
ORDER BY t1.name, t2.name
2 Likes
hi
hope this helps
Condensed form
CREATE TABLE People (
Name VARCHAR(50)
);
INSERT INTO People (Name) VALUES
('Frank'),
('Heather'),
('John'),
('Eric');
data
t-sql
SELECT p1.Name AS Person,
STRING_AGG(p2.Name, ', ') AS RelatedNames
FROM People p1
CROSS JOIN People p2
WHERE p1.Name <> p2.Name
GROUP BY p1.Name;
Output
Like this?
select a.resource_id, b.resource_id2
from ahsrelvalue_with_names a
cross join ahsrelvalue_with_names b
where a.rel_value='100910'
order by a.resource_id,b.resource_id
I get invalid object name for ahsrelvalue_with_names
Thanks, could be possible, but I need all names to appear individually in separate rows. Not 3 of them in the same record
Why is this included? The original post shows names with themselves, e.g., ‘Frank’ with ‘Frank’.
1 Like
SELECT q.name,t.name
FROM people q, people t;
shorter code than cross join
1 Like
hi PMJ
possible causes for your error
- Wrong DB
- Missing schema
- Object not created
- Typo/case issue
- Check existence
Fred Unique -- no likes fo this 
Scott ... too much nit picky stuff
Depends on whether you consider an invalid result “nit picky” I guess 
hi Scott
do not mean anything bad or ill intention on my part
typical stuff of people
there are several means several things ways for a person to handle or say things
for example
- ignore ... could have been a genuine mistake
- suggest much better solutions
- word it differently
- people have microscopic eyes on others but not on themselves ( when you see what they do its funny )
this is another PHD area of my interest .. could say neuro science of people
please Scott no bad evil intentions on my part just extremely casual
Yes, thank you. It actually works! Easier than expected! There is however, another problem: I need to create these relations within departments(rel_value), which means that I need one expression for dpt 100910 and another for dpt 100665, and so forth. Using UNION, I will merge all expressions into the same table. I cannot write dpt between 100000 and 199999, I need spesific relations for each dpt, ie 300 unions for 300 departments.
Do you have any simplification ideas?
select a.client, a.resource_id as Meg,b.resource_id as Kollega,a.rel_value
from ahsrelvalue a,ahsrelvalue b
where a.rel_attr_id='MNAL'
and a.client='NR'
and a.rel_value='100910' and b.rel_value='100910'
and a.date_to>getdate ()and b.date_to>getdate ()
union
select a.client,a.resource_id as Meg,b.resource_id as Kollega,a.rel_value
from ahsrelvalue a,ahsrelvalue b
where a.rel_attr_id='MNAL'
and a.client='NR'
and a.rel_value='100665' and b.rel_value='100665'
and a.date_to>getdate ()and b.date_to>getdate ()
union ………
hi
hope this helps
Simplification Idea: Use a Self‑Join with Department
Instead of repeating the query for each department,
SELECT a.client,
a.resource_id AS Meg,
b.resource_id AS Kollega,
a.rel_value
FROM ahsrelvalue a
JOIN ahsrelvalue b
ON a.rel_value = b.rel_value
AND a.client = b.client
WHERE a.rel_attr_id = 'MNAL'
AND a.client = 'NR'
AND a.date_to > GETDATE()
AND b.date_to > GETDATE();
If you want only certain departments, add: AND a.rel_value IN ('100910','100665', ... )
1 Like
Yes! Seems to be working! Thank’s a lot!