CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Stacky
Stacky

Reputation: 67

How to show rows horizontally instead of vertically?

I am getting records like this:

Rev-- Status-- Remarks-- RemarksDate -- User
0     prepared  done     12/jul/2019    John
0     approved  done     12/jul/2019    Amy
0     Issued    done     13/jul/2019    Tom
1     REOPENED  done     14/jul/2019    John
1     Prepared  done     14/jul/2019    Ritz

but I am trying to display like this

Rev   PreparedBy APprovedBy IssuedBy REOPENEDBy  Date
0     John       Amy        Tom      John        onlyissuedate
1     John       Ritz       Marine   Riya            //

I am trying to achive this without changing the table structure but cannot.

Query:

        Select * from table

No need to include remarks in the 2nd format.

For prepared I am using status# 0 and for approve 1, for issue 3, for reopened 5.

Upvotes: 0

Views: 86

Answers (3)

Khairul Alam
Khairul Alam

Reputation: 1336

This may help.

DECLARE @TempData TABLE(Rev VARCHAR(10),Status VARCHAR(50),Remarks VARCHAR(50),RemarksDate DATE,UserName VARCHAR(50))
INSERT INTO  @TempData VALUES
('0',     'Prepared',  'done',     '12/jul/2019',    'John'),
('0',     'Approved',  'done',     '12/jul/2019',    'Amy '),
('0',     'Issued  ',  'done',     '13/jul/2019',    'Tom '),
('0',     'Reopened',  'done',     '14/jul/2019',    'John'),
('1',     'Reopened',  'done',     '14/jul/2019',    'Marine'),
('1',     'Prepared',  'done',     '14/jul/2019',    'John'),
('1',     'Approved',  'done',     '12/jul/2019',    'Ritz '),
('1',     'Issued',    'done',     '14/jul/2019',    'John')

SELECT pvtValue.Rev
      ,pvtValue.Prepared PreparedBy
      ,pvtValue.Approved ApprovedBy
      ,pvtValue.Issued IssuedBy
      ,pvtValue.Reopened ReopenedBy
      ,t.RemarksDate IssueDate
FROM( 
     SELECT Rev,Status,UserName
     FROM  @TempData

     ) pvt
     PIVOT (MAX(UserName) FOR STATUS IN (Prepared,Approved,Issued, Reopened )
     ) pvtValue 
     LEFT JOIN @TempData t ON pvtValue.Issued = t.UserName AND pvtValue.Rev = t.Rev AND t.Status = 'Issued'

enter image description here

Upvotes: 1

Ahmad
Ahmad

Reputation: 12737

You can simply achieve that without having to JOIN any copies of the same table. All you need is a way to convert each row value into a column, (we will use CASE WHEN for that), and we will combine that with the aggregate function MAX which will ignore NULL values we specify in the case above.

select 
    rev,
    max(case when status='prepared' then user        else null end) preparedBy,
    max(case when status='approved' then user        else null end) approvedBy,
    max(case when status='Issued'   then user        else null end) IssuedBy  ,
    max(case when status='REOPENED' then user        else null end) reopenedBy,
    max(case when status='Issued'   then remarksdate else null end) date
from
    table_name
group by
    rev
order by
    rev

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

A pivot query approach should work here:

SELECT
    rh.Rev,
    MAX(CASE WHEN rh.Status = 'prepared' THEN rh.RemarksByName END) AS PreparedBy,
    MAX(CASE WHEN rh.Status = 'approved' THEN rh.RemarksByName END) AS ApprovedBy,
    MAX(CASE WHEN rh.Status = 'Issued'   THEN rh.RemarksByName END) AS IssuedBy,
    MAX(CASE WHEN rh.Status = 'REOPENED' THEN rh.RemarksByName END) AS reopenedBy,
    MAX(CASE WHEN rh.Status = 'Issued'   THEN rh.RemarksDate END)   AS Date
FROM RemarksHisotry rh
INNER JOIN inspectionReport ir
    ON ir.InspectionReportID = rh.InspectionReportID
INNER JOIN Status s
    ON s.StatusID = rh.[Status]
WHERE
    ir.VelosiReportNo = @InspectionReportNo
GROUP BY
    rh.Rev
ORDER BY
    rh.Rev;

Upvotes: 2

Related Questions