Help with dynamically creating columns from rows in another table

[edmana]

Hey everyone,

I have two tables:

Contacts

Number​

Name​

Address​

...

Groups

ContactID​

Group Name​

I need to create a query that outputs a single list of all info for contacts and a column for each group which can change over time (Group names and whether a contact is in the group)

Here is what I came up with so far:

[tt]SELECT a.Number,
SUM(CASE GroupName WHEN 'Newsletter E-Mail' THEN 1 ELSE 0 END) AS 'NewsletterE-Mail'
FROM Contacts a Left Outer Join Groups b on a.Number = b.Number
GROUP BY a.Number[/tt]

I have only used 1 group so far to test.

There are a few issues:
1. This query doesn't give all info, only the Contact Number and groups. I tried to add more columns to return but got an error because they are not grouped.
2. This query isn't dynamic in terms of group names. If the group name changes or groups are added/deleted, the query won't run right.

Can someone point me in the right direction on this? I was looking at pivot table queries and got a bit lost for my application.

Thanks so much!
Ed

 

[Olaf Doschke]

SQL group by means aggregation of all group data into one record
Reporting of groups means no aggregation, just nesting all details of a group.

That's two completely disparate concepts.

All you do is query all data ungrouped, joined together with left joins. Let a report group your data visually for you, e.g. list all contacts within a group or all groups a contact belongs to. Your SQL query result will have all head data of a group repeatedly, and a report will use that just once for a group header, suppress al repeated values, that's how this results in a hierarchically grouped listing.

Don't confuse that with aggregating data, you want to see all details and thus you don't want to group by SQL wise.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[edmana]

There is no report. I need this for import into another application.

I need a query that will dynamically give me:

All contact columns in the contact table and a column for each row that makes up the groups table with a 1 or a 0 whether it is in the group or not.

 

[Olaf Doschke]

Then your basis still is a simple join of contacts and groups pivoted by group names and since that is dynamic and not a static list of group names you need to go for a dynamic SQL solution, like

https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

You should rather have three tables: Groups, Contacts and Group Contacts or members, a table linking which contacts are in which group.

Create Table #Contacts (ID int, Name varchar(100));
Create Table #Groups (ID int, "Group Name" varchar(100));
Create Table #GroupMembers (ContactID int, GroupID int);

Insert into #Contacts values (1, 'contact 1'), (2, 'contact 2'), (3, 'contact 3');
Insert into #Groups values (1, 'group 1'), (2, 'group 2'), (3, 'group 3');
Insert into #GroupMembers values (1, 1), (1,2), (2,1), (3,2), (3,3);

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX), @one as int;

Set @one =1;

SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME("Group Name")
  FROM (SELECT g."Group Name" FROM #Groups AS g
  INNER JOIN #GroupMembers AS gm ON gm.GroupID = g.ID
  INNER JOIN #Contacts AS c ON c.ID = gm.ContactID
  GROUP BY g."Group Name") AS x;
SET @sql = N'
SELECT p.Name as Contact,' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT g."Group Name", gm.ContactID, c.Name
   FROM #Groups AS g
  INNER JOIN #GroupMembers AS gm ON gm.GroupID = g.ID
  INNER JOIN #Contacts AS c ON c.ID = gm.ContactID
) AS j
PIVOT
(
  Count(ContactID) FOR "Group Name" IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';

--PRINT @sql;
EXEC sp_executesql @sql;

drop table #contacts
drop table #groups
drop table #groupmembers

Result:
[pre]Contact group 1 group 2 group 3
contact 1 1 1 0
contact 2 1 0 0
contact 3 0 1 1[/pre]

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name

 

[edmana]

Olaf,

I got this to work as a stored procedure. How do I get this into a table in SQL as I need to bring into a view to link to other data? I tried a bunch of stuff and am stuck

I am using a version of SQL >= 2012

Thanks!
Ed

 

[Olaf Doschke]

I'd guess table valued function, but not sure what you did. As you already contacted me two dayss ago, I mailed back to that.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

The problem with a table-valued function for such a script is, that it doesn't allow a dynamic table definition.

Well, as you said (in a mail) this is just a one-off job and you don't need it to work universally:

1. esiest way to get the result out into anything is selecting the rseult you see in SSMS and paste it into excel, there is no code needed for that
2. use any client side language able to call a stored proc you have created, and store the result as XML, excel or whatever to continue from there
3. Create an SSIS package taking the result and storing it into a final "sink" of flat file or excel. I doubt SSIS package is for you, that'd mean a steep leraning curve before you get there.

But perhaps the easiest way is instead of the last step

EXEC sp_executesql @sql;

Really just print the sql and extend it to
I. first create a table or temp table
2. insert the query result into that table
3. continue from there, joining views, exporting that table to anywhere else

To demonstrate how to do that: In my sample code I have the following query created:

SELECT p.Name as Contact,p.[group 1], p.[group 2], p.[group 3]
FROM
(
  SELECT g."Group Name", gm.ContactID, c.Name
   FROM #Groups AS g
  INNER JOIN #GroupMembers AS gm ON gm.GroupID = g.ID
  INNER JOIN #Contacts AS c ON c.ID = gm.ContactID
) AS j
PIVOT
(
  Count(ContactID) FOR "Group Name" IN ([group 1],[group 2],[group 3])
) AS p;

I just used PRINT @sql and then copy&pasted that SQL query from the messages tab

This lets me see I need a table with columns Contact,[group 1], [group 2], [group 3], so I could write:

Create Table #Contacts (ID int, Name varchar(100));
Create Table #Groups (ID int, "Group Name" varchar(100));
Create Table #GroupMembers (ContactID int, GroupID int);

Insert into #Contacts values (1, 'contact 1'), (2, 'contact 2'), (3, 'contact 3');
Insert into #Groups values (1, 'group 1'), (2, 'group 2'), (3, 'group 3');
Insert into #GroupMembers values (1, 1), (1,2), (2,1), (3,2), (3,3);


[highlight #FCE94F]CREATE TABLE ##contactresult (Contact varchar(100),[group 1] bit, [group 2] bit, [group 3] bit)

INSERT INTO ##contactresult[/highlight]
SELECT p.Name as Contact,p.[group 1], p.[group 2], p.[group 3]
FROM
(
  SELECT g."Group Name", gm.ContactID, c.Name
   FROM #Groups AS g
  INNER JOIN #GroupMembers AS gm ON gm.GroupID = g.ID
  INNER JOIN #Contacts AS c ON c.ID = gm.ContactID
) AS j
PIVOT
(
  Count(ContactID) FOR "Group Name" IN ([group 1],[group 2],[group 3])
) AS p;

The only new code is highlighted, your mileage varies, as your result surely is wider, but the name list is coming from PRINT @sql, it's not much work.

Now you can query from ##contactresult or join it in a view, or export it. Make it a real table and not just temp table, if you need it more permanent, but I already chose the more permanent type of temp tables, a global temp table. Even when it's gone, you always have the script to create it.

That's to conclude with such things, when you create sql programmatically for one-off usage it's always an option to only do the hard part automatically, add to it manually and then store that for future reference. You could also extend the code to create a variable aside of @columns, say @columns2, which adds the columns data type bit or integer to all field names, so you don't need to do that manually.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name