March 22, 2017 at 1:11 pm
Hi I have a data set like below;
Name Role
John Admin
John Dev
Adam Admin
Adam Dev
===========================
It needs to appear like below;
Name Role Name Role
John Admin Adam Admin
John Dev Adam Dev
can some please help me with logic?
Thank you in advance!!
March 22, 2017 at 1:29 pm
No, because a complete solution would violate 1NF, which is not allowed in SQL Server. (All records must have the same number of fields.)
Second, you haven't specified the logic for combining the rows. It appears that you want all records with the same ROLE on the same row, but there could be a less obvious reason for combining those records.
Finally, you haven't given any logic for the ordering within the rows. I assume that you want them in presentation order, but sets are unordered, and there is no such thing as a presentation order.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 23, 2017 at 9:28 am
Since there is a requirement I have to bring the dataset like I mentioned. Can you help modifying the data like I showed whenever a new name is given to that table.
March 23, 2017 at 10:23 am
You haven't addressed any of the points that I raised in my response, so it still isn't clear exactly what you are trying to do. Giving you a solution would be pure guesswork at this point.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 23, 2017 at 11:54 am
I understand this more as reporting request than table design problem. Let's guess 😉 your table remains as is - two columns (Name,Role). I will now do the work you should have done:
CREATE TABLE TestRoles
(
Name varchar(5) NOT NULL
, [Role] varchar(5) NOT NULL
, CONSTRAINT PK_TestRoles PRIMARY KEY (Name, [Role])
)
GO
INSERT INTO TestRoles (Name, [Role])
VALUES
('John', 'Admin'), ('Jane', 'Admin' ), ('John', 'Dev' )
, ('Adam', 'Admin'), ('Adam', 'Dev' ), ('Mike', 'Dev' )
, ('Jane', 'Dev' ), ('Peter', 'Dev' ), ('Chris', 'Admin' )
;
Then you run a simple query:SELECT Name, [Role]
FROM TestRoles
ORDER BY Name,[Role]
;
-- Result:
Name Role
----- -----
Adam Admin
Adam Dev
Jane Admin
Jane Dev
John Admin
John Dev
Mike Dev
(7 row(s) affected)
What you probably (guess again 😉 ) want the output like this:
Name Role Name Role Name Role Name Role
----- ----- ----- ----- ----- ----- ----- -----
Adam Admin Jane Admin John Admin Mike Dev
Adam Dev Jane Dev John Dev
Well, output like that really does not make much sense, does it. You can achive that by cut/paste into Excel from the simple query mentioned above. perhaps you would like the output as following:Admin Dev
----- -----
Adam Adam
Chris NULL
Jane Jane
John John
NULL Mike
NULL Peter
Peace of cake, eh:
SELECT
[Admin] = MAX(CASE WHEN [Role] = 'Admin' THEN Name ELSE NULL END)
, Dev = MAX(CASE WHEN [Role] = 'Dev' THEN Name ELSE NULL END)
FROM TestRoles
GROUP BY Name
ORDER BY Name
;
Those were my educated guesses. Now the ball is in your field, to rephrase the question.
🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply