Reverse Table In SQL

Question

I have query:

SELECT DISTINCT temp.ID,request.RequestTypeID
FROM @MyTempTable6 as temp
JOIN dbo.FingerMachineUsers as fingeruser
ON temp.UserNo = fingeruser.ID
JOIN dbo.AppUsers as appuser
ON appuser.Id = fingeruser.UserId
LEFT JOIN dbo.Requests as request
ON request.UserId = fingeruser.UserId

And result of it:

How can I create table like this:

ID|RequestTypeID1|RequestTypeID2
1 |             4|             5

If your levels are fixed you can think of case statement as well. — Gaurav, Apr 26 '18 at 10:35
Is it Dynamic? Meaning, are there more than two values? (4,5) — DxTx, Apr 26 '18 at 10:36
Ya It Dynamic if ID have more requestTypeID then to table have more like this — Thien Vu, Apr 26 '18 at 10:40
There are two rows for ID 1. (The column shouldn't be called ID when it's not unique, by the way.) What makes you put value 4 in the first column and value 5 in the second in your result? Why not vice versa? Is there any rule you want to apply? — Thorsten Kettner, Apr 26 '18 at 15:43

score 0 · Accepted Answer · answered Apr 26 '18 at 10:40

If you have only two values, then the simplest method is aggregation:

SELECT t.ID, MIN(r.RequestTypeID), MAX(r.RequestTypeID)
FROM @MyTempTable6 t JOIN
     dbo.FingerMachineUsers fu
     ON t.UserNo = fu.ID JOIN
     dbo.AppUsers au
     ON au.Id = fu.UserId LEFT JOIN
     dbo.Requests r
     ON r.UserId = fu.UserId
GROUP BY t.ID;

If you have have a variable number of values that you want to present, then the query is much more complicated.

score 0 · Answer 2 · answered Apr 26 '18 at 10:44

0

PIVOT operator could be your best friend, if there are always only 2 values each...

answered Apr 26 '18 at 10:44

Antonio

535
10
26

i never used PIVOT before , can u help me ?? – Thien Vu Apr 26 '18 at 10:46

score 0 · Answer 3 · answered Apr 26 '18 at 11:07

Try this query...

Disclaimer: This code is purely based on this answer. (https://stackoverflow.com/a/10404455/6327676)

DECLARE @cols  AS NVARCHAR(max), 
        @query AS NVARCHAR(max); 

SET @cols = Stuff((SELECT DISTINCT ',' + Quotename(stff.requesttypeid)
                   FROM   TableName stff
                   FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '' 
            ) 
SET @query = 'SELECT ID, '+ @cols
             + ' FROM (select ID, RequestTypeId FROM TableName) x pivot (MAX(RequestTypeId) FOR RequestTypeId in (' 
             + @cols + ')) p' 

EXECUTE(@query)

score 0 · Answer 4 · answered Apr 26 '18 at 12:04

Try this code it will work according to the result you want. Firstly you need to dump @MyTempTable6 table into #temptable then set RequestTypeID columns as comma seperated in @colums varible then set the columnname in @Requestcolumns variable,Then use pivot.

DECLARE @colums AS NVARCHAR(max)
DECLARE @Requestcolumns AS NVARCHAR(max)
DECLARE @query AS NVARCHAR(max);


SELECT DISTINCT temp.ID,request.RequestTypeID
into #temptable
FROM @MyTempTable6 as temp
JOIN dbo.FingerMachineUsers as fingeruser
ON temp.UserNo = fingeruser.ID
JOIN dbo.AppUsers as appuser
ON appuser.Id = fingeruser.UserId
LEFT JOIN dbo.Requests as request
ON request.UserId = fingeruser.UserId  

SET @colums = Stuff((SELECT DISTINCT ',' +Quotename(tab.RequestTypeID)
                   FROM   #temptable tab
                   FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '' 
            )      

SET @Requestcolumns = Stuff((SELECT DISTINCT ',' +Quotename(tab.RequestTypeID) +' AS ',+Quotename('RequestTypeID'+CONVERT(varchar(100),tab.RequestTypeID))
                   FROM   #temptable tab
                   FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '' 
            ) 

SET @query = 'SELECT ID, '+@Requestcolumns
             + ' FROM (select ID, RequestTypeID FROM #temptable) x pivot (MAX(RequestTypeID) FOR RequestTypeID in (' 
             + @colums + ')) p' 

EXECUTE(@query) 

DROP table #temptable

Reverse Table In SQL

4 Answers4