• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 91
  • Last Modified:

MS SQL - Output to columns equally.

I am trying to convert input data like this:
1000
1001
1002
1003
1004
1005



then output as:

C1                               C2                           C3                               C4  
1000                          1002                          1004                            1005     
1001                          1003

Open in new window


This will be in a Stored Procedure.
So far, I am dividing the number of results to see:
6 Nodes / 4 = 1 full row.

I do 2 options for output. I either have the data in the 4 columns, or I assign a column identity to it such as C1R1, C2R1

The end output from this is actually a print file and the columns need to fill them in this fashion when there are 6 or 600 items.
1
egarrison
Asked:
egarrison
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
i think this expression
( row_number() over ( order by colum ) -1) / 4
will return 4 riws with the same value...
1
 
bcnagelCommented:
Is it fair to assume that the input data coming in is always in numeric order? So, to extend your example with more rows, will we always get this kind of data?

1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011

We won't get smaller numbers coming after larger ones, right? Assuming the above assumption is correct, is it also correct that you want to see the numbers above parsed into rows in the following way?

C1          C2          C3          C4
----------- ----------- ----------- ------
1000        1002        1004        1005
1001        1003                
1006        1008        1010        1011
1007        1009                

Open in new window

0
 
bcnagelCommented:
If my assumptions in the comment above are correct, you should be able to do something like the following.


Create a sample table:
select *
	into #SampleData
	from (
		values 
		(1000),
		(1001),
		(1002),
		(1003),
		(1004),
		(1005),
		(1006),
		(1007),
		(1008),
		(1009),
		(1010),
		(1011)
		) as der(Val)

Open in new window


Use row_number() and dense_rank() to group and display:
;with cte_RowNumberAndRank as (
	select der.Val, der.OverallRowNumber, dense_rank() over(order by (OverallRowNumber-1)/6) as [Rank]
		from (
			select *, row_number() over(order by Val) as OverallRowNumber
				from #SampleData s
			) as der
	)

,cte_RowInRank as (
	select r.Val, r.[Rank], row_number() over(partition by [Rank] order by r.OverallRowNumber) as RowInRank
			from cte_RowNumberAndRank r
	)

-- 
select der.[Rank] as GroupNumber, der.OutputRow, 
	max(case when der.RowInRank in (1,2) then Val end) as C1,
	max(case when der.RowInRank in (3,4) then Val end) as C2,
	max(case when der.RowInRank = 5 then Val end) as C3,
	max(case when der.RowInRank = 6 then Val end) as C4
	from (
		select rr.Val, rr.[Rank], rr.RowInRank, 
			case when rr.RowInRank in (1,3,5,6) then 1 else 2 end as OutputRow
			from cte_RowInRank rr
		) der
	group by [Rank], OutputRow
	order by [Rank], OutputRow

Open in new window

1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
egarrison, do you still need help with this question?
0
 
egarrisonCEO / CTOAuthor Commented:
Great logic.  I ended up using a slightly modified version of this. Thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now