Horizontal Rows as Vertical Columns in SQL Server

Question

I have this requirement where I need to present horizontal rows as vertical columns in SQL Server native query.

For example, my select query returns 100 records for the select query using inner join.

select  XX, XX etc  FROM TAB1 con inner join TAB2 reg on con.id = reg.reference_id WHERE con.is_active=1 and
            con.approval_status=3 order by XXX

[![enter image description here][1]][1]

Now I want my query returning 1 records for 10 records where dynamic columns name should be created based on the business_ids for example 670_h_current, 671_h_current etc for all those 10 records. The result should be returned as a single query with 10 dynamic columns each represents a row value for business id and hcurrent.

I tried to use the Pivot table, but it does not seem working.Is there a way to manipulate the SQLs to return the required number of records. I am doing it at the moment in java but fetching all the records, then transforming. However, pagnation is an issue and my interface is slower as it picks a lot of records. I wanted the select query fetches a reduced view of records.

Any idea? Any help much appreciated

Current Select output

ID          DEP LEG MAS     EXE  DES        P1  BUS_ID  ATTE
==================================================================
COI_AP002   215 216 4071    758 clients.    NULL    673 1
COI_AP002   215 216 4071    758 clients.    NULL    672 0

Expected Output

ID          DEP LEG MAS     EXE  DES    P1    ATTE_673  ATTE_672
==========================================================================
COI_AP002   215 216 4071    758 clients. NULL   1          0

Answer 1

Here's something to get you started. Then, you need to do a little work, to learn the concept, and to get it to do exactly what you want it to do.

--Drop Table dbo.Lateral
CREATE TABLE dbo.Lateral
( Id  VARCHAR(100),
  Dep int,
  Des VARCHAR(100),
  Bus_ID int,
  Atte int)

--Delete from Account

INSERT INTO dbo.Lateral
  (Id, Dep, Des, Bus_ID, Atte)
VALUES
  ('COI_AP002','215','clients',673,1)
INSERT INTO dbo.Lateral
  (Id, Dep, Des, Bus_ID, Atte)
VALUES
 ('COI_AP002','215','clients',672,0)

Select *
From Lateral

select 
x.ID,
sum(case when t.bus_id = '673' then 1 end) atte_673,
sum(case when t.bus_id =' 672' then 0 end) atte_672
from lateral t
cross apply (values 
('COI_AP002' , t.bus_ID)
) as x(ID, val)
group by x.ID