February 11, 2019 at 10:10 am
Hi All,
Could you please help me in get the script for below scenario:
Table Creation and Insertion:
CREATE TABLE [dbo].[Testresult](
[Name] [varchar](50) NULL,
[Admin] [int] NULL,
[Developer] [int] NULL,
[Tester] [int] NULL,
[Manager] [int] NULL,
[EndUser] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Testresult] VALUES ('XYZ',1,0,0,0,0)
INSERT INTO [dbo].[Testresult] VALUES('YZX',0,1,0,0,0)
INSERT INTO [dbo].[Testresult] VALUES('ZXY',1,0,0,0,0)
INSERT INTO [dbo].[Testresult] VALUES('ABC',0,0,1,0,0)
INSERT INTO [dbo].[Testresult] VALUES('BCA',0,0,0,1,0)
INSERT INTO [dbo].[Testresult] VALUES('CAB',0,0,0,0,1)
select * from [Testresult]
Below is the results
| Name | Admin | Developer | Tester | Manager | EndUser |
| XYZ | 1 | 0 | 0 | 0 | 0 |
| YZX | 0 | 1 | 0 | 0 | 0 |
| ZXY | 1 | 0 | 0 | 0 | 0 |
| ABC | 0 | 0 | 1 | 0 | 0 |
| BCA | 0 | 0 | 0 | 1 | 0 |
| CAB | 0 | 0 | 0 | 0 | 1 |
I need results in below format. Wherever value is 1, respective name should be displayed under that columns, like shown below.
| Admin | Developer | Tester | Manager | EndUser |
| XYZ | YZX | ABC | BCA | CAB |
| ZXY |
Please help me to get the query.
I tried below method but could not get much help.
Select name as Admin into #temp1 from [Testresult] where Admin=1
Select name as Developer into #temp2 from [Testresult] where Developer=1
Select name as Tester into #temp3 from [Testresult] where Tester=1
Select name as Manager into #temp4 from [Testresult] where Manager=1
Select name as EndUser into #temp5 from [Testresult] where EndUser=1
Select * from #temp1,#temp2,#temp3,#temp4,#temp5
Below is the results:
| Admin | Developer | Tester | Manager | EndUser |
| XYZ | YZX | ABC | BCA | CAB |
| ZXY | YZX | ABC | BCA | CAB |
Please help me here
Thank You.
Thank You.
Regards,
Raghavender Chavva
February 11, 2019 at 10:34 am
Raghavender Chavva - Monday, February 11, 2019 10:10 AMHi All,Could you please help me in get the script for below scenario:
Orginal Table structure:
NAME Col1 Col2 Col3 Col4 x 1 0 0 0 y 0 1 0 0 z 0 0 1 0 a 1 0 0 0 b 0 0 0 1 c 0 0 0 0 I need results in below format:
Col1 Col2 Col3 Col4 x y z b a Please help me to get the query.
Thank You.
You've been here long enough. You should know that you need to post sample data in a consumable format for people to show some support. Also post what you have tried.
This screams bad design all over the place. I don't understand why would you store or show data like that. You might want to consider a redesign of your tables.
February 11, 2019 at 10:49 am
Luis Cazares - Monday, February 11, 2019 10:34 AMRaghavender Chavva - Monday, February 11, 2019 10:10 AMHi All,Could you please help me in get the script for below scenario:
Orginal Table structure:
NAME Col1 Col2 Col3 Col4 x 1 0 0 0 y 0 1 0 0 z 0 0 1 0 a 1 0 0 0 b 0 0 0 1 c 0 0 0 0 I need results in below format:
Col1 Col2 Col3 Col4 x y z b a Please help me to get the query.
Thank You.
You've been here long enough. You should know that you need to post sample data in a consumable format for people to show some support. Also post what you have tried.
This screams bad design all over the place. I don't understand why would you store or show data like that. You might want to consider a redesign of your tables.
I think you also might know some of the requirements come as adhoc requests and for those adhoc requirements nobody consider redesign of tables.
Coming to the point some times the most experience guy also requires help from others.
Let me know consumable format means ?
Thank You.
Regards,
Raghavender Chavva
February 11, 2019 at 11:13 am
Raghavender Chavva - Monday, February 11, 2019 10:49 AMLet me know consumable format means ?
Read the articles linked in my signature on "how to post data/code on a forum to get the best help"
February 11, 2019 at 11:15 am
Luis Cazares - Monday, February 11, 2019 11:13 AMRaghavender Chavva - Monday, February 11, 2019 10:49 AMLet me know consumable format means ?
Read the articles linked in my signature on "how to post data/code on a forum to get the best help"
Thank you. will check reframe the question.
Thank You.
Regards,
Raghavender Chavva
February 11, 2019 at 1:00 pm
You also need to specify the logic used to determine which values appear together. NOTE: Tables represent sets, which are unordered. The display order is not a valid option.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 13, 2019 at 3:32 am
/*I have tried using Row Number and Left outer join to display the data in the required format
1. Derived column called RID will be created and used to map the columns*/
SELECT
ISNULL([tblAdmin].[Name],'') [Admin]
,ISNULL([tblDeveloper].[Name],'') Developer
,ISNULL([tblTester].[Name],'') Tester
,ISNULL([tblManager].[Name],'') Manager
,ISNULL([tblEndUser].[Name],'') EndUser
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RID FROM [Testresult] --Create Row ID for list of rows
) tblMain
LEFT OUTER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RID, CASE [Admin] WHEN 1 THEN [Name] ELSE NULL END [Name] FROM [Testresult] WHERE [Admin] = 1
) [tblAdmin] ON tblMain.RID = [tblAdmin].RID
LEFT OUTER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RID,CASE [Developer] WHEN 1 THEN [Name] ELSE NULL END [Name] FROM [Testresult] WHERE [Developer] = 1
) [tblDeveloper] ON tblMain.RID = [tblDeveloper].RID
LEFT OUTER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RID,CASE [Tester] WHEN 1 THEN [Name] ELSE NULL END [Name] FROM [Testresult] WHERE [Tester] = 1
) [tblTester] ON tblMain.RID = [tblTester].RID
LEFT OUTER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RID,CASE [Manager] WHEN 1 THEN [Name] ELSE NULL END [Name] FROM [Testresult] WHERE [Manager] = 1
) [tblManager] ON tblMain.RID = [tblManager].RID
LEFT OUTER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RID,CASE [EndUser] WHEN 1 THEN [Name] ELSE NULL END [Name] FROM [Testresult] WHERE [EndUser] = 1
) [tblEndUser] ON tblMain.RID = [tblEndUser].RID
WHERE
[tblAdmin].RID IS NOT NULL
OR [tblDeveloper].RID IS NOT NULL
OR [tblTester].RID IS NOT NULL
OR [tblManager].RID IS NOT NULL
OR [tblEndUser].RID IS NOT NULL
February 13, 2019 at 8:32 am
k.karthik0889 - Wednesday, February 13, 2019 3:32 AM/*I have tried using Row Number and Left outer join to display the data in the required format
1. Derived column called RID will be created and used to map the columns*/SELECT
ISNULL([tblAdmin].[Name],'') [Admin]
,ISNULL([tblDeveloper].[Name],'') Developer
,ISNULL([tblTester].[Name],'') Tester
,ISNULL([tblManager].[Name],'') Manager
,ISNULL([tblEndUser].[Name],'') EndUser
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RID FROM [Testresult] --Create Row ID for list of rows
) tblMain
LEFT OUTER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RID, CASE [Admin] WHEN 1 THEN [Name] ELSE NULL END [Name] FROM [Testresult] WHERE [Admin] = 1
) [tblAdmin] ON tblMain.RID = [tblAdmin].RID
LEFT OUTER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RID,CASE [Developer] WHEN 1 THEN [Name] ELSE NULL END [Name] FROM [Testresult] WHERE [Developer] = 1
) [tblDeveloper] ON tblMain.RID = [tblDeveloper].RID
LEFT OUTER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RID,CASE [Tester] WHEN 1 THEN [Name] ELSE NULL END [Name] FROM [Testresult] WHERE [Tester] = 1
) [tblTester] ON tblMain.RID = [tblTester].RID
LEFT OUTER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RID,CASE [Manager] WHEN 1 THEN [Name] ELSE NULL END [Name] FROM [Testresult] WHERE [Manager] = 1
) [tblManager] ON tblMain.RID = [tblManager].RID
LEFT OUTER JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) RID,CASE [EndUser] WHEN 1 THEN [Name] ELSE NULL END [Name] FROM [Testresult] WHERE [EndUser] = 1
) [tblEndUser] ON tblMain.RID = [tblEndUser].RID
WHERE
[tblAdmin].RID IS NOT NULL
OR [tblDeveloper].RID IS NOT NULL
OR [tblTester].RID IS NOT NULL
OR [tblManager].RID IS NOT NULL
OR [tblEndUser].RID IS NOT NULL
This is a very expensive way of doing it. Unpivoting and pivoting the data would be a lot more efficient.
February 13, 2019 at 9:12 am
This doesn't pivot the data but is a fast (and a bit more simple than yours) way to get the subset of information back that you want.select t.Name,x.Type, x.Value
from [Testresult] t
cross apply(values('Admin',t.Admin),('Developer',t.Developer),('Tester',t.Tester),('Manager',t.Manager),('EndUser',t.EndUser)) x(Type,Value)
where x.Value =1
Just put it in a CTE and write a query on the CTE that does what you want.
February 13, 2019 at 9:22 am
Luis Cazares - Wednesday, February 13, 2019 8:32 AMThis is a very expensive way of doing it. Unpivoting and pivoting the data would be a lot more efficient.
I was thinking the same thing, although, you might be able to skip the unpivot if you can guarantee that exactly ONE of the columns will equal 1.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 13, 2019 at 9:40 am
I just noticed that there's sample data posted now. Here's a solution that will pivot the data.
WITH Unpivoted AS(
SELECT *, ROW_NUMBER() OVER( PARTITION BY Col ORDER BY (SELECT NULL)) rn
FROM [Testresult]
CROSS APPLY (VALUES('Admin', [Admin]),
('Developer', Developer),
('Tester', Tester),
('Manager', [Manager]),
('EndUser', [EndUser]))u(Col, Val)
WHERE Val = 1
)
SELECT MAX( CASE WHEN Col = 'Admin' THEN [Name] END) AS [Admin],
MAX( CASE WHEN Col = 'Developer' THEN [Name] END) AS Developer,
MAX( CASE WHEN Col = 'Tester' THEN [Name] END) AS Tester,
MAX( CASE WHEN Col = 'Manager' THEN [Name] END) AS Manager,
MAX( CASE WHEN Col = 'EndUser' THEN [Name] END) AS EndUser
FROM Unpivoted
GROUP BY rn
February 13, 2019 at 9:46 pm
Luis Cazares - Wednesday, February 13, 2019 9:40 AMI just noticed that there's sample data posted now. Here's a solution that will pivot the data.
WITH Unpivoted AS(
SELECT *, ROW_NUMBER() OVER( PARTITION BY Col ORDER BY (SELECT NULL)) rn
FROM [Testresult]
CROSS APPLY (VALUES('Admin', [Admin]),
('Developer', Developer),
('Tester', Tester),
('Manager', [Manager]),
('EndUser', [EndUser]))u(Col, Val)
WHERE Val = 1
)
SELECT MAX( CASE WHEN Col = 'Admin' THEN [Name] END) AS [Admin],
MAX( CASE WHEN Col = 'Developer' THEN [Name] END) AS Developer,
MAX( CASE WHEN Col = 'Tester' THEN [Name] END) AS Tester,
MAX( CASE WHEN Col = 'Manager' THEN [Name] END) AS Manager,
MAX( CASE WHEN Col = 'EndUser' THEN [Name] END) AS EndUser
FROM Unpivoted
GROUP BY rn
Thanks.
February 14, 2019 at 6:44 am
Luis Cazares - Wednesday, February 13, 2019 9:40 AMI just noticed that there's sample data posted now. Here's a solution that will pivot the data.
WITH Unpivoted AS(
SELECT *, ROW_NUMBER() OVER( PARTITION BY Col ORDER BY (SELECT NULL)) rn
FROM [Testresult]
CROSS APPLY (VALUES('Admin', [Admin]),
('Developer', Developer),
('Tester', Tester),
('Manager', [Manager]),
('EndUser', [EndUser]))u(Col, Val)
WHERE Val = 1
)
SELECT MAX( CASE WHEN Col = 'Admin' THEN [Name] END) AS [Admin],
MAX( CASE WHEN Col = 'Developer' THEN [Name] END) AS Developer,
MAX( CASE WHEN Col = 'Tester' THEN [Name] END) AS Tester,
MAX( CASE WHEN Col = 'Manager' THEN [Name] END) AS Manager,
MAX( CASE WHEN Col = 'EndUser' THEN [Name] END) AS EndUser
FROM Unpivoted
GROUP BY rn
Thank You Luis. It helped me a lot.
Thank You.
Regards,
Raghavender Chavva
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply