Pivot

1

How can i use PIVOT operator for the following SQL Script to produce the same result?

declare @startdate DATETIME, @enddate DATETIME
declare @site varchar(3)

set @startdate = '2022-03-01'
set @enddate = '2022-03-31'
set @site = '105'

create table #temp_table
(
#site_id varchar(3),
#tran_date varchar(10),
#label_type varchar(20),
#tran_number INT
)

insert into #temp_table
(
#site_id,
#tran_date,
#label_type,
#tran_number
)
(
select rTranslog.Site,
convert(varchar(10),rTranslog.IssueDate,103),
rTranslog.LabelType,
count(*)
from rTranslog
left join rPatients on rTranslog.PatId = rPatients.PatID
left join rConsultant on rTranslog.Consultant = rConsultant.consultant
join rProduct on rProduct.NSVCode = rTranslog.NSVCode
where rTranslog.IssueDate >= @startdate
and rTranslog.IssueDate <= @enddate
and rTranslog.Site = @site
group by rTranslog.Site,
convert(varchar(10),rTranslog.IssueDate,103),
rTranslog.LabelType
)
select #site_id as 'Site',
#tran_date as 'Tran Date',
coalesce(MAX(CASE WHEN #label_type = 'O' THEN #tran_number END),'') AS [OutPatient],
coalesce(MAX(CASE WHEN #label_type = 'I' THEN #tran_number END),'') AS [InPatient],
coalesce(MAX(CASE WHEN #label_type = 'D' THEN #tran_number END),'') AS [Discharge],
coalesce(MAX(CASE WHEN #label_type = 'L' THEN #tran_number END),'') AS [Leave],
coalesce(MAX(CASE WHEN #label_type = 'C' THEN #tran_number END),'') AS [Civas],
coalesce(MAX(CASE WHEN #label_type = 'M' THEN #tran_number END),'') AS [Manufactured],
coalesce(MAX(CASE WHEN #label_type = 'P' THEN #tran_number END),'') AS [Picking Ticket],
coalesce(MAX(CASE WHEN #label_type = 'S' THEN #tran_number END),'') AS [Stock],
coalesce(MAX(CASE WHEN #label_type = 'T' THEN #tran_number END),'') AS [PN],
coalesce(MAX(CASE WHEN #label_type = 'W' THEN #tran_number END),'') AS [Unknown]
from #temp_table
group by #site_id,
#tran_date
drop table #temp_table

2

As your data is not reproducable for me I cannot test the code I think it should be.

SELECT
	pvt.[Site],
	pvt.[Tran Date],
	pvt.[O] AS [OutPatient],
	pvt.[I] AS [InPatient]
FROM
(
/* SOURCETABLE */
SELECT 
	rTranslog.[Site],
	CONVERT(VARCHAR(10),rTranslog.IssueDate,103),
	rTranslog.LabelType,
	tran_number
FROM rTranslog
	LEFT OUTER JOIN rPatients 
		ON rTranslog.PatId = rPatients.PatID
	LEFT OUTER JOIN rConsultant 
		ON rTranslog.Consultant = rConsultant.consultant
	INNER JOIN rProduct 
		ON rProduct.NSVCode = rTranslog.NSVCode
WHERE rTranslog.IssueDate >= @startdate
	AND rTranslog.IssueDate <= @enddate
	AND rTranslog.[Site] = @site
) tbl
PIVOT
(	
	MAX(tran_number) FOR label_type IN ([O],[I])
) pvt;
3

Thank you very much. Let me try another way:
The script below:
select rTranslog.Site as [Site],
convert(varchar(10),rTranslog.IssueDate,103) as [Date],
rTranslog.LabelType as [Label Type],
count(*) as [Trans No]
from rTranslog
left join rPatients on rTranslog.PatId = rPatients.PatID
left join rConsultant on rTranslog.Consultant = rConsultant.consultant
join rProduct on rProduct.NSVCode = rTranslog.NSVCode
where rTranslog.IssueDate >= '2024-02-01'
and rTranslog.IssueDate <= '2024-02-29'
and rTranslog.Site = '105'
group by rTranslog.Site,
convert(varchar(10),rTranslog.IssueDate,103),
rTranslog.LabelType
order by rTranslog.Site,
convert(varchar(10),rTranslog.IssueDate,103),
rTranslog.LabelType

Will produce an output like:

Site Date Label Type Trans No
105 01/02/2024 D 1
105 01/02/2024 O 234
105 01/02/2024 S 10
105 02/02/2024 O 267

I would like to use pivot operator to have the output like:

|105|02/02/2024|S|2|
|105|03/02/2024|O|66|
|105|05/02/2024|O|291|
|105|05/02/2024|P|1|
|105|05/02/2024|S|2|
|105|06/02/2024|I|1|
|105|06/02/2024|O|200|
|105|06/02/2024|P|2|
|105|06/02/2024|S|11|

So that I have the transaction date on one row and transaction types as columns.

Thank you

4

Your question is not clear to me so I cannot help.