Select Command - 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

SQL Server 2005 Forums

SQL Server Administration (2005)

Select Command

Author

Topic

matrixmind
Starting Member

9 Posts

Posted - 2010-04-16 : 06:27:58

Hi Experts
Suppose We have Table Str. like
RollNo Name
1 A
2 B
3 C
4 D

i want the output like
RollNo Name
1 A
RollNo Name
2 B
RollNo Name
3 C
RollNo Name
4 D

i.e For each row of table Repeat Columns

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-04-16 : 06:52:06

Declare @i int
set @i=1
While @i<=4
begin
Select RollNo , Name from Str where RollNo =@i
set @i=@i+1
End

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-16 : 11:32:57

quote:
Originally posted by matrixmind

Hi Experts
Suppose We have Table Str. like
RollNo Name
1 A
2 B
3 C
4 D

i want the output like
RollNo Name
1 A
RollNo Name
2 B
RollNo Name
3 C
RollNo Name
4 D

i.e For each row of table Repeat Columns

Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-16 : 12:03:41

quote:
Originally posted by matrixmind

Hi Experts
Suppose We have Table Str. like
RollNo Name
1 A
2 B
3 C
4 D

i want the output like
RollNo Name
1 A
RollNo Name
2 B
RollNo Name
3 C
RollNo Name
4 D

i.e For each row of table Repeat Columns

It's very easy to do in your calling application if you want to. This is a presentation issue. Don't burden the database with this.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

matrixmind
Starting Member

9 Posts

Posted - 2010-04-17 : 01:11:08

quote:
Originally posted by Transact Charlie

quote:
Originally posted by matrixmind

Hi Experts
Suppose We have Table Str. like
RollNo Name
1 A
2 B
3 C
4 D

i want the output like
RollNo Name
1 A
RollNo Name
2 B
RollNo Name
3 C
RollNo Name
4 D

i.e For each row of table Repeat Columns

It's very easy to do in your calling application if you want to. This is a presentation issue. Don't burden the database with this.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

i want to do it from backend not front end i know it is possible from front end. but how i can do it from backend

Dinesh Sharma
Matrix Solution
Sr.Software Engg.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-19 : 04:21:37

why do you want to do it in the back end? It will be slower and harder to debug. There are no good ways. If the result set is a static result (you always know what the column headers will be then this probably would work).


/*
Hi Experts
Suppose We have Table Str. like
RollNo Name
1 A
2 B
3 C
4 D

i want the output like
RollNo Name
1 A
RollNo Name
2 B
RollNo Name
3 C
RollNo Name
4 D

*/

DECLARE @foo TABLE (
	[RollNo] INT
	, [Name] CHAR(1)

	PRIMARY KEY ([RollNo])
	)

INSERT @foo ([rollNo], [Name])
      SELECT 1, 'A'
UNION SELECT 2, 'B'
UNION SELECT 3, 'C'
UNION SELECT 4, 'D'

SELECT
	[rollNo]
	, [name]
FROM
	(
	SELECT
		ROW_NUMBER() OVER (ORDER BY [RollNo]) AS [rowPos]
		, 1 AS [Result]
		, CAST([RollNo] AS VARCHAR(50)) AS [rollNo]
		, CAST([Name] AS VARCHAR(50)) AS [Name]
	FROM
		@foo f

	UNION ALL SELECT
		[rowPos]
		, 0 AS [Result]
		, 'rollNo' AS [rollNo]
		, 'Name' AS [Name]
	FROM
		(
		SELECT
			ROW_NUMBER() OVER (ORDER BY [RollNo]) AS [rowPos]
			, CAST([RollNo] AS VARCHAR(50)) AS [rollNo]
			, CAST([Name] AS VARCHAR(50)) AS [Name]
		FROM
			@foo
		)
		f
	WHERE
		f.[rowPos] > 1
	)
	res
ORDER BY
	res.[rowPos]
	, res.[Result]

But look how complicated that is! You lose any type information (you have to cast everything to varchars. Do it in the app -- this is stupid in the database.

Results:


rollNo name
------ ----
1      A
rollNo Name
2      B
rollNo Name
3      C
rollNo Name
4      D

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources