query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2013-12-23 : 03:46:16

Dear Team Kindly help me in following query

declare @table1 table
(name varchar )

INSERT INTO @TABLE1(NAME)
VALUES ('A') , ('B'), ('C' ) , ('D'), ('E') , ('F') , ('G') , ('H')

select * from @table1

now i want to show record like below grouping of 3 column

COL1 COL2 COL3
A B C
D E F
G H

Kindly suggest

prithvi nath pandey

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-12-23 : 04:42:26

[code]

DECLARE @table1 TABLE (name VARCHAR)

DECLARE @n INT = 3

INSERT INTO @TABLE1(NAME) VALUES ('A') , ('B'), ('C' ) , ('D'), ('E') , ('F') , ('G') , ('H')

SELECT
rn2
, MAX(CASE WHEN rn = 0 THEN name ELSE NULL END)
, MAX(CASE WHEN rn = 1 THEN name ELSE NULL END)
, MAX(CASE WHEN rn = 2 THEN name ELSE NULL END)
FROM (
SELECT
*,
rn = (ROW_NUMBER() OVER (ORDER BY NAME) - 1)/@n,
rn2 = (ROW_NUMBER() OVER (ORDER BY NAME) - 1) % @n + 1
FROM @table1
)src
GROUP BY rn2
[/code]

edit format

nextaxtion
Yak Posting Veteran

54 Posts

Posted - 2013-12-23 : 05:13:58

hi waterduck aur query provide result quite well but please help in change the seqeuence of values

Required output :

COL1 COL2 COL3
A B C
D E F
G H

this query output :

a d i
b e j
c f

prithvi nath pandey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-23 : 10:03:46

[code]
declare @T table
(
COL1 char(1),
COL2 char(1),
COL3 char(1)
)
insert @t
values('A','B','C'),
('D','E','F'),
('G', 'H',NULL)
SELECT [1],[2],[3]
FROM
(
SELECT Col,Val,ROW_NUMBER() OVER (PARTITION BY Col ORDER BY Val) AS Rn
FROM @T
UNPIVOT (val FOR Col IN (COL1,COL2,COL3))u
)m
PIVOT (MAX(Val) FOR Rn IN ([1],[2],[3]))p

output
---------------------------------------
A D G
B E H
C F NULL

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-23 : 12:44:58

Simplicity...

DECLARE	@Table1 TABLE
	(
		Name VARCHAR(10)
	); 

INSERT	@Table1
	(
		Name
	)
VALUES	('A'),
	('B'),
	('C'),
	('D'),
	('E'),
	('F'),
	('G'),
	('H');

-- First requirement
WITH cteSource
AS (
	SELECT	Name,
		ROW_NUMBER() OVER (ORDER BY Name) - 1 AS rn
	FROM	@Table1
)
SELECT		MAX(CASE WHEN rn % 3 = 0 THEN Name ELSE '' END) AS Col1,
		MAX(CASE WHEN rn % 3 = 1 THEN Name ELSE '' END) AS Col2,
		MAX(CASE WHEN rn % 3 = 2 THEN Name ELSE '' END) AS Col3
FROM		cteSource
GROUP BY	rn / 3;

-- Second requirement
WITH cteSource
AS (
	SELECT	Name,
		ROW_NUMBER() OVER (ORDER BY Name) - 1 AS rn
	FROM	@Table1
)
SELECT		MAX(CASE WHEN rn / 3 = 0 THEN Name ELSE '' END) AS Col1,
		MAX(CASE WHEN rn / 3 = 1 THEN Name ELSE '' END) AS Col2,
		MAX(CASE WHEN rn / 3 = 2 THEN Name ELSE '' END) AS Col3
FROM		cteSource
GROUP BY	rn % 3;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources