SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[test](
	[name] [varchar](50) NULL,
	[length] [int] NULL
) ON [PRIMARY]

GO

insert into test (name, length) values ('balk1', 7)
insert into test (name, length) values ('balk1', 6)
insert into test (name, length) values ('balk1', 9)
insert into test (name, length) values ('stof1', 6)
insert into test (name, length) values ('stof2', 6)
insert into test (name, length) values ('stof3', 6)
insert into test (name, length) values ('stof4', 6)
insert into test (name, length) values ('stof5', 6)
insert into test (name, length) values ('stof5', 7)
insert into test (name, length) values ('stof6', 6)
insert into test (name, length) values ('stof7', 6)
insert into test (name, length) values ('stof8', 6)
insert into test (name, length) values ('stof9', 6)
insert into test (name, length) values ('stof9', 7)
insert into test (name, length) values ('stof10', 6)
insert into test (name, length) values ('stof11', 6)
insert into test (name, length) values ('stof12', 6)

select * from test
SET ANSI_PADDING OFF
GO

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL='SELECT '+CHAR(13)+CHAR(10)+
STUFF((SELECT ','+CHAR(13)+CHAR(10)+'NULLIF(MAX(CASE WHEN [name] = '+CHAR(39)+[name]+CHAR(39)+
       ' THEN [length] ELSE '+CHAR(39)+CHAR(39)+' END),'+CHAR(39)+CHAR(39)+
       ') AS '+QUOTENAME([name])
       FROM (SELECT DISTINCT [name]
             FROM test
             )a([name])
       ORDER BY LEN([name]),[name]
       FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)'),1,3,'')+CHAR(13)+CHAR(10)+'FROM [dbo].[test]'+
CHAR(13)+CHAR(10)+'GROUP BY [length];';

EXECUTE sp_executesql @SQL;

balk1       stof1       stof2       stof3       stof4       stof5       stof6       stof7       stof8       stof9       stof10      stof11      stof12
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
6           6           6           6           6           6           6           6           6           6           6           6           6
7           NULL        NULL        NULL        NULL        7           NULL        NULL        NULL        7           NULL        NULL        NULL
9           NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL        NULL

insert into test (name, length) values ('stof13', 7)
insert into test (name, length) values ('stof13', 8)

item	Balk1	Stof1	Stof2	Stof3	Stof4	Stof5	Stof6	Stof7	Stof8
number of items needed	4	6	2	4	4	1	16	12	4
length of each item (mm)	5138,15	1478	2000	2698,24	1479,36	3350	800	1333	1473
available length (mm)	6000	6000	6000	6000	6000	6000	6000	6000	6000
available length (mm)	7000	7000	7000	NULL	NULL	NULL	NULL	NULL	NULL
available length (mm)	9000	9000	9000	NULL	NULL	NULL	NULL	NULL	NULL
Suggested length	6000	9000	6000	6000	6000	6000	6000	6000	6000
[color=red]nr of items to buy	4	1	1	2	1	1	3	3	1[/color

CREATE TABLE [dbo].[testtest](
	[item] [varchar](50) NULL,
	[balk1] [varchar](50) NULL,
	[stof1] [varchar](50) NULL,
	[stof2] [varchar](50) NULL,
	[stof3] [varchar](50) NULL,
	[stof4] [varchar](50) NULL,
	[stof5] [varchar](50) NULL,
	[stof6] [varchar](50) NULL,
	[stof7] [varchar](50) NULL,
	[stof8] [varchar](50) NULL,
) ON [PRIMARY]

GO

insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8) 
values ('number of items needed', 4, 6, 2, 4, 4, 1, 16, 12, 4)
insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8) 
values ('length of each item (mm)', 5138, 1478, 2000, 2698, 1479, 3350, 800, 1333, 1473)
insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8) 
values ('available length (mm)', 6000, 6000, 6000, 6000, 6000, 6000, 6000, 6000, 6000)
insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8) 
values ('available length (mm)', 7000, 7000, 7000, null, null, null, null, null, null)
insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8) 
values ('available length (mm)', 9000, 9000, 9000, null, null, null, null, null, null)
insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8) 
values ('Suggested length', 6000, 9000, 6000, 6000, 6000, 6000, 6000, 6000, 6000)
insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8) 
values ('nr of items to buy', 4, 1, 1, 2, 1, 1, 3, 3, 1)