how to display a blank row after random numbers

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

Transact-SQL (2005)

how to display a blank row after random numbers

Author

Topic

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-10-22 : 10:04:42

I would like to display a blank row or SUM every time Num_Col changes
*(the problem is that the numbers in my column are unpredictiable.. it needs to be dynamic)

This is my desired output:

Num_Col
31
31
31

1234
1234

5
5
5
5
5

99
99
99

Thanks any help is appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-22 : 10:20:15

This is a kind of formatting the output. SQL is not made for this.

Sorry
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 10:25:08

seems like a formatting issue. where do you want to show data?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-22 : 10:37:30

Why not? Let him shot himself in the foot if he wants to.
It is his birthgiven right to do so...

DECLARE	@Sample TABLE
	(
		data INT
	)

INSERT	@Sample
SELECT	  31 UNION ALL
SELECT	   5 UNION ALL
SELECT	1234 UNION ALL
SELECT	   5 UNION ALL
SELECT	  99 UNION ALL
SELECT	   5 UNION ALL
SELECT	  31 UNION ALL
SELECT	  99 UNION ALL
SELECT	   5 UNION ALL
SELECT	  31 UNION ALL
SELECT	1234 UNION ALL
SELECT	   5 UNION ALL
SELECT	  99

SELECT		data
FROM		(
			SELECT	CAST(data AS VARCHAR(12)) AS data,
				0 AS t,
				data AS y
			FROM	@Sample

			UNION ALL

			SELECT	DISTINCT
				'',
				1,
				data
			FROM	@Sample
		) AS d
ORDER BY	y,
		t

E 12°55'05.63"
N 56°04'39.26"

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-10-22 : 10:48:55

the problem is that the numbers in my column are unpredictiable.. it needs to be dynamic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-22 : 10:50:09

[code]SELECT {Your column name here}
FROM (
SELECT CAST({Your column name here} AS VARCHAR(12)) AS {Your column name here},
0 AS t,
{Your column name here} AS y
FROM {Your table name here}

UNION ALL

SELECT DISTINCT
'',
1,
{Your column name here}
FROM {Your table name here}
) AS d
ORDER BY y,
t[/code]

E 12°55'05.63"
N 56°04'39.26"

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-10-22 : 12:00:04

this code is not working for me Peso, but thanks for your assistance anyways

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-22 : 12:44:56

Do you mind telling us why you think the code is not working?
It works very well according to your original specs.

E 12°55'05.63"
N 56°04'39.26"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-10-22 : 12:58:28

he probably doesn't have a column named {Your column name here} in the database...

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-10-22 : 13:08:15

maybe im inserting my code in wrong.. could you insert it for me? I get the error (T0.DocNum cannot be bound)
thanks

Select T0.DocNum, T1.Quantity
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

My code ^^ I would like a blank row every time T0.DocNum changes

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-23 : 05:21:48

quote:
Originally posted by DMarmolejos

maybe im inserting my code in wrong.. could you insert it for me? I get the error (T0.DocNum cannot be bound)
thanks

Select T0.DocNum, T1.Quantity
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

My code ^^ I would like a blank row every time T0.DocNum changes

Is the table ORDR has column DocNum?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-23 : 05:37:30

Select T0.DocNum, T1.Quantity
FROM ORDR as T0 INNER JOIN RDR1 as T1 ON T0.DocEntry = T1.DocEntry

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources