It is necessary to use all occurrences of a row in a table to output values ​​from these rows into a separate column. Suppose that we know the maximum number of entries and have formed a table correctly, without forgetting to sort by one of the columns (this is a separate question and I sort of figured out the dynamic number of columns). I will explain with an example:

table T:

element | value

item 1 | five

item 1 | 6

item 2 | 7

item 3 | eight

item 2 | 9

Need to bring to mind

Element | entry 1 | entry 2

item1 | 5 | 6

item2 | 7 | 9

item3 | eight

  • if you figure out the dynamic number of columns, what is the problem with you? - teran
  • With what exactly in these columns to request. That is, I can see in advance the maximum number of entries, form a query string through the loop with the required number of columns, and therefore execute (query_string). But what exactly to add to this line at each iteration is not entirely clear. - Vitaly Yandulov
  • one
    Dak you are familiar with the operator PIVOT in the end? If not, he will solve your problem. It if you slip a line of request, then you will not make in any way, only. - teran

2 answers 2

formally, your task is not to transpose in a mathematical sense, but to create a pivot table. For these purposes in TSQL there is a PIVOT operator (as well as the reverse UNPIVOT ).
Unfortunately, this operator does not work with a dynamic set of columns. Therefore, for such cases, you have to build a query as a string and then execute it. What in general does not quite fit your task of creating a view ( view ). Therefore, you can count on stored procedures.

Initial data

 create table #t (item varchar(10), value int); INSERT INTO #t VALUES ('item1', 5), ('item1', 6), ('item2', 7),('item3', 8),('item2', 9);

Columns, as you have already written, we collect dynamically in a row. For all this, we define several variables (here @maxN is the number of result columns):

 DECLARE @maxN int = (select top 1 count(value) from #t group by item order by count(value) desc); DECLARE @n int = 1; DECLARE @nStr VARCHAR(3); DECLARE @strSel varchar(1024) = '', @strFor varchar(512) = '', @sql nvarchar(MAX) = '';

Dynamically form lines in a loop:

 WHILE (@n <= @maxN) BEGIN SET @nStr = cast(@n as nvarchar); SET @strSel += ',[' + @nStr + ']' + ' as col' + @nStr; SET @strFor += (case when @n > 1 then ',' else '' end) + '[' + @nStr + '] '; SET @n += 1; END;

Next, glue everything into a SQL query string and complete it.

 SET @sql = 'SELECT pt.item' + @strSel + ' FROM ( select item, value, row_number() over (partition by item order by value) as colIdx from #t ) as data PIVOT ( SUM(data.value) FOR colIdx in (' + @strFor + ') ) as pt'; print @sql; execute sp_executesql @sql; drop table #t;

The request received is as follows:

 SELECT pt.item,[1] as col1,[2] as col2 FROM ( select item, value, row_number() over (partition by item order by value) as colIdx from #t ) as data PIVOT ( SUM(data.value) FOR colIdx in ([1] ,[2]) ) as pt

And the results of its implementation:

 item col1 col2 ---------- ----------- ----------- item1 5 6 item2 7 9 item3 8 NULL
  • Eh, your work would be a little bit earlier :) I didn’t waste my time in those days, waiting for all kind work to do. As a result, the collector (I will not give his work) forms the query string. The same main points (number of columns, row formation cycle). Although, at the end I had to make another external request, but it is executed in a second, so I will not begin to redo it, although I studied your calculations for the experience. And yes, I had to be stored in the procedure due to the dynamic number of columns. Thank! - Vitaly Yandulov
  • @VitaliyYandulov is not for nothing! You would answer the comments to the question more quickly, and the answer would be earlier :) By the way, by the way, we still need to study the effectiveness of the pivot , regarding the number of columns, it may not be an effective way. A similar table can be constructed through a case operator in a select item, sum(case when colIdx = 1 then value else 0 end) as col1, .... group by item type, such as select item, sum(case when colIdx = 1 then value else 0 end) as col1, .... group by item - teran
  • That's it, through the case and did. But then another external request was required for the table: item1 | NULL | 6; item1 | 5 | NULL; item2 | NULL | 9 ; item2 | 7 | NULL; item3 | 8 | NULL; Bring in the form of God - Vitaly Yandulov

Perhaps you are satisfied with the following option:

 create table #t (item varchar(10), num int); insert #t values('item1', 5), ('item1', 6), ('item2', 7), ('item3', 8), ('item2', 9); select item, ( select stuff( (select ',' + convert(varchar(10), num) from #t as t1 where t1.item = t2.item for xml path ('')) , 1, 1, '')) from #t as t2 group by item; drop table #t;

The result will be obtained in two columns in the form:

 item1 5,6 item2 7,9 item3 8
  • it was not necessary to combine values ​​into strings, but rather the opposite? - teran
  • No Unfortunately. I have an escalation of applications that go to the table and I need to make a view of where to take the entire first entry (and assign the rest to the side when and which department took on each application, that is, there are several columns for each entry). And now I broke my head how to do it. - Vitaly Yandulov