1
I have two tables:
table1:
Manager | USER
--------+------
John | Tom
John | David
John | Marry
James | Henry
table2: Now is empty, but must be like this:
Manager | User1 | User2 | User3
--------+-------+-------+-------
John | Tom | David | Marry
James | Henry | null | null
Is it possible?
Sarunas Radzevicius
Posted 2016-01-15T12:58:41.217
Reputation: 11
0
If table2 structure is fixed as you showed you can insert your expected result into it by a query like this:
INSERT INTO table2 (Manager, User1, User2, User3)
SELECT
Manager,
MAX(CASE WHEN seq = 1 THEN [USER] END) AS User1,
MAX(CASE WHEN seq = 2 THEN [USER] END) AS User2,
MAX(CASE WHEN seq = 3 THEN [USER] END) AS User3
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Manager ORDER BY [USER]) AS seq
FROM table1) t
GROUP BY
Manager;
But, If table2 structure is not fixed you need to use Dynamic SQL.
shA.t
Posted 2016-01-15T12:58:41.217
Reputation: 449
its certainly possible, but your constraints matter. personally I'd write a sproc that returns all the employees from table1 for a given supervisor. then a cursor over the distinct list of supervisors would let you get all the subordinates and insert them into the new table. There are probably ways to do it with just select code, but your (poorly designed) tables will make that more difficult. I would recommend you stick with the table1 design and avoid the table2 design, as it breaks second normal form. – Frank Thomas – 2016-01-15T13:06:26.963
If you just need to generate a report in that format to a temp table or something, look into
SELECT COALESCEsyntax as I've generated reports in SQL Server as you describe just for a report but I'd not do this for a permanent table design but that should get you started. Check out dba.stackexchange.com as well. – Pimp Juice IT – 2016-01-15T13:12:41.523Yes, you just query and add to a temporay table.. You'll need to share what you've tried so we can help based upon your existing code :) – Dave – 2016-01-15T14:56:28.317