Cross tabulation that limits the number of Columns

Cross-data report that limits the number of Columns

-- Sample data:
Create Table Test (
Factoryid varchar (20 ),
Bagid int,
Roll int,
Number numeric (9,1 ),
Unique (bagid, roll ))
Insert test select'm-CS-11 #6/green', 1, 1, 86
Union all select'm-CS-11 #6/green', 59.5
Union all select'm-CS-11 #6/green', 31.2
Union all select'm-CS-11 #6/green', 42
Union all select'm-CS-11 #6/green', 31
Union all select'm-CS-11 #6/green', 114.3

Union all select'm-CS-11 #6/green', 101
Union all select'm-CS-11 #6/green', 83.9
Union all select'm-CS-11 #6/green', 97.5
Union all select'm-CS-11 #6/green', 105.4

Union all select'm-CS-11 #6/green', 103
Union all select'm-CS-11 #6/green', 128.5
Union all select'm-CS-11 #6/green', 74.7
Union all select'm-CS-11 #6/green', 107

Union all select'm-CS-11 #6/green', 73.4
Union all select'm-CS-11 #6/green', 100
Union all select'm-CS-11 #6/green', 141.5
Go

Problem description:
Bagid and roll values are unique. Roll columns must be horizontally displayed, and only four columns are displayed for each record. Redundant line breaks are automatically generated. For the sample data, the result is as follows (rolls is the number of records ):
Factoryid bagid rolls N1 N2 N3 N4 total
-----------------------------------------------------------------------------------------------
M-CS-11 #6/Green 1 6 86.0 59.5 31.2 42.0
M-CS-11 #6/Green 1 31.0 114.3 364.0
M-CS-11 #6/Green 2 4 101.0 83.9 97.5 105.4 387.8
M-CS-11 #6/Green 3 4 103.0 128.5 74.7 107.0 413.2
M-CS-11 #6/Green 4 3 73.4 100.0 141.5 314.9
Total 1479.9

(The number of affected rows is 6)

-- Query Processing code
Select a. factoryid, A. bagid,
Rolls = case
When a. Roll = 0 then cast (B. rolls as varchar)
Else ''end,
A. N1, A. N2, A. N3, A. N4,
Total = case
When a. Roll is null then cast (A. Total as varchar)
When a. Roll = (B. Rolls-1)/4 then cast (B. Total as varchar)
Else ''end
From (
Select factoryid = case
When grouping (factoryid) = 1 then 'Total'
Else factoryid end,
Bagid = case
When grouping (factoryid) = 1 then''
Else cast (bagid as varchar) end,
N1 = case
When grouping (factoryid) = 1 then''
Else cast (sum (Case roll % 4 When 0 Then number end) as varchar) end,
N2 = case
When grouping (factoryid) = 1 then''
Else isnull (cast (sum (Case roll % 4 when 1 then number end) as varchar), '') end,
N3 = case
When grouping (factoryid) = 1 then''
Else isnull (cast (sum (Case roll % 4 when 2 then number end) as varchar), '') end,
N4 = case
When grouping (factoryid) = 1 then''
Else isnull (cast (sum (Case roll % 4 when 3 then number end) as varchar), '') end,
Total = sum (number ),
Roll = roll/4
From (
Select factoryid, bagid, number,
Roll = (select count (distinct roll)
From Test
Where factoryid = A. factoryid
And bagid = A. bagid
And roll <A. Roll)
From test
) A group by factoryid, bagid, roll/4 with Rollup
Having grouping (factoryid) = 1 or grouping (Roll/4) = 0
)
Left join (
Select factoryid, bagid,
Rolls = count (*),
Total = sum (number)
From Test
Group by factoryid, bagid
) B on A. factoryid = B. factoryid
And a. bagid = B. bagid
Go

Original post address