Gap to the left - 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

Transact-SQL (2005)

Gap to the left

Author

Topic

jeremiev
Starting Member

5 Posts

Posted - 2008-01-14 : 08:53:09

Hi developers and architects,

I would like to know if it's possible to do "a gap to the left" in transact sql...

I have a table with:

ID Level1 Level2 Level3 Level4
1 NULL NULL 1 2
2 NULL 1 2 3

My results should be:

ID Level1 Level2 Level3 Level4
1 1 2 2 2
2 1 2 3 3

Currently I'm using a cursor to do that but I haven't good performance.

Do you have ideas?

Thanks a lot,

Jérémie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-14 : 08:54:10

can you explain how to get to the result that you need ?

KH
[spoiler]Time is always against us[/spoiler]

jeremiev
Starting Member

5 Posts

Posted - 2008-01-14 : 09:03:30

I'm using a cursor that contains the first table (with null value)

While Level1 is null then
Level1 = Level2
Level2 = Level3
Level3 = Level4
end

that's row by row

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-14 : 09:57:12

[code]DECLARE @Sample TABLE (ID INT, Level1 INT, Level2 INT, Level3 INT, Level4 INT)

INSERT @Sample
SELECT 1, NULL, NULL, 10, 20 UNION ALL
SELECT 2, NULL, 11, 12, 13

SELECT f.*
FROM (
SELECT p.ID,
p.Value,
'Level' + CONVERT(VARCHAR(12), ROW_NUMBER() OVER (PARTITION BY p.ID ORDER BY p.Level)) AS [Level]
FROM @Sample AS s
UNPIVOT (
[Value]
FOR [Level] IN (s.Level1, s.Level2, s.Level3, s.Level4)
) AS p
) AS y
PIVOT (
MAX(y.Value)
FOR y.[Level] IN ([Level1], [Level2], [Level3], [Level4])
) AS f
ORDER BY f.ID[/code]

E 12°55'05.25"
N 56°04'39.16"

jeremiev
Starting Member

5 Posts

Posted - 2008-01-14 : 10:06:12

But I would like to obtain

ID|Level1|Level2|Level3|Level4
1|10| 20| 20| 20
2| 11| 12| 13| 13

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-14 : 10:44:30

Hmmm...

DECLARE	@Sample TABLE (ID INT, Level1 INT, Level2 INT, Level3 INT, Level4 INT)

INSERT	@Sample
SELECT	1, NULL, NULL, 10, 20 UNION ALL
SELECT	2, NULL, 11, 12, 13

SELECT		f.ID,
		f.Level1,
		COALESCE(f.Level2, f.Level1) AS Level2,
		COALESCE(f.Level3, f.Level2, f.Level1) AS Level3,
		COALESCE(f.Level4, f.Level3, f.Level2, f.Level1) AS Level4
FROM		(
			SELECT	p.ID,
				p.Value,
				'Level' + CONVERT(VARCHAR(12), ROW_NUMBER() OVER (PARTITION BY p.ID ORDER BY p.Level)) AS [Level]
			FROM	@Sample AS s
			UNPIVOT	(
					[Value]
					FOR	[Level] IN (s.Level1, s.Level2, s.Level3, s.Level4)
				) AS p
		) AS y
PIVOT		(
			MAX(y.Value)
			FOR	y.[Level] IN ([Level1], [Level2], [Level3], [Level4])
		) AS f
ORDER BY	f.ID

Please timetest and report back the time with CURSOR and the time with this piece of code.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-14 : 11:30:46

This test data

INSERT	@Sample
SELECT	1, NULL, NULL, 10, 20 UNION ALL
SELECT	3, NULL, 7, NULL, 5 UNION ALL
SELECT	2, NULL, 11, 12, 13

will generate this result

ID	Level1	Level2	Level3	Level4
1	10	20	20	20
2	11	12	13	13
3	 7	 5	 5	 5

E 12°55'05.25"
N 56°04'39.16"

jeremiev
Starting Member

5 Posts

Posted - 2008-01-15 : 03:01:57

quote:
Originally posted by Peso

Please timetest and report back the time with CURSOR and the time with this piece of code.

Hi Peso,
First of all, Thanks for your help!

Below the code with your query and mine with a cursor.
My table KPI contains 2000 records

declare  @temp_result table
	(	id_KPI  int,
		level_01       int,
		level_02       int,
		level_03       int,
		level_04       int,
		level_05       int,
		level_06       int,
		level_07       int,
		level_08       int,
		level_09       int,
		level_10       int,
		level_11       int,
		level_12       int,
		level_13       int,
		level_14       int,
		level_15       int,

		level_01_display_order   int,
		level_02_display_order   int,
		level_03_display_order   int,
		level_04_display_order   int,
		level_05_display_order   int,
		level_06_display_order   int,
		level_07_display_order   int,
		level_08_display_order   int,
		level_09_display_order   int,
		level_10_display_order   int,
		level_11_display_order   int,
		level_12_display_order   int,
		level_13_display_order   int,
		level_14_display_order   int,
		level_15_display_order   int		
	)



	declare  @temp  table
	(	id_KPI  int,
		level_01       int,
		level_02       int,
		level_03       int,
		level_04       int,
		level_05       int,
		level_06       int,
		level_07       int,
		level_08       int,
		level_09       int,
		level_10       int,
		level_11       int,
		level_12       int,
		level_13       int,
		level_14       int,
		level_15       int,

		level_01_display_order   int,
		level_02_display_order   int,
		level_03_display_order   int,
		level_04_display_order   int,
		level_05_display_order   int,
		level_06_display_order   int,
		level_07_display_order   int,
		level_08_display_order   int,
		level_09_display_order   int,
		level_10_display_order   int,
		level_11_display_order   int,
		level_12_display_order   int,
		level_13_display_order   int,
		level_14_display_order   int,
		level_15_display_order   int
	)




	insert into  @temp
		select
			L1.id_KPI,
			
			L15.id_KPI  as  level_01,
			L14.id_KPI  as  level_02,
			L13.id_KPI  as  level_03,
			L12.id_KPI  as  level_04,
			L11.id_KPI  as  level_05,
			L10.id_KPI  as  level_06,
			L9.id_KPI   as  level_07,
			L8.id_KPI   as  level_08,
			L7.id_KPI   as  level_09,
			L6.id_KPI   as  level_10,
			L5.id_KPI   as  level_11,
			L4.id_KPI   as  level_12,
			L3.id_KPI   as  level_13,
			L2.id_KPI   as  level_14,
			L1.id_KPI   as  level_15,

			L15.nn_display_order  as  level_01_display_order,
			L14.nn_display_order  as  level_02_display_order,
			L13.nn_display_order  as  level_03_display_order,
			L12.nn_display_order  as  level_04_display_order,
			L11.nn_display_order  as  level_05_display_order,
			L10.nn_display_order  as  level_06_display_order,
			L9.nn_display_order   as  level_07_display_order,
			L8.nn_display_order   as  level_08_display_order,
			L7.nn_display_order   as  level_09_display_order,
			l6.nn_display_order   as  level_10_display_order,
			l5.nn_display_order   as  level_11_display_order,
			l4.nn_display_order   as  level_12_display_order,
			l3.nn_display_order   as  level_13_display_order,
			l2.nn_display_order   as  level_14_display_order,
			L1.nn_display_order   as  level_15_display_order
		from
			KPI_hierarchy  L1   left join
			KPI_hierarchy  L2  on  L1.id_parent_KPI = l2.id_KPI		left join
			KPI_hierarchy  L3  on  L2.id_parent_KPI = l3.id_KPI		left join
			KPI_hierarchy  L4  on  L3.id_parent_KPI = l4.id_KPI		left join
			KPI_hierarchy  L5  on  L4.id_parent_KPI = l5.id_KPI		left join
			KPI_hierarchy  L6  on  L5.id_parent_KPI = l6.id_KPI		left join
			KPI_hierarchy  L7  on  L6.id_parent_KPI = l7.id_KPI		left join
			KPI_hierarchy  L8  on  L7.id_parent_KPI = l8.id_KPI		left join
			KPI_hierarchy  L9  on  L8.id_parent_KPI = l9.id_KPI		left join
			KPI_hierarchy  L10 on  L9.id_parent_KPI = l10.id_KPI		left join
			KPI_hierarchy  L11 on  L10.id_parent_KPI = l11.id_KPI		left join
			KPI_hierarchy  L12 on  L11.id_parent_KPI = l12.id_KPI		left join
			KPI_hierarchy  L13 on  L12.id_parent_KPI = l13.id_KPI		left join
			KPI_hierarchy  L14 on  L13.id_parent_KPI = l14.id_KPI		left join
			KPI_hierarchy  L15 on  L14.id_parent_KPI = l15.id_KPI			
			


insert into @temp_result	
SELECT		f.id_KPI,
		f.Level1,
		COALESCE(f.Level2, f.Level1) AS Level2,
		COALESCE(f.Level3, f.Level2, f.Level1) AS Level3,
		COALESCE(f.Level4, f.Level3, f.Level2, f.Level1) AS Level4,
		COALESCE(f.level5, f.Level4, f.Level3, f.Level2, f.Level1) AS Level5,
		COALESCE(f.level6, f.level5, f.Level4, f.Level3, f.Level2, f.Level1) AS Level6,
		COALESCE(f.level7, f.level6, f.level5, f.Level4, f.Level3, f.Level2, f.Level1) AS Level7,
		COALESCE(f.level8, f.level7, f.level6, f.level5, f.Level4, f.Level3, f.Level2, f.Level1) AS Level8,
		COALESCE(f.level9, f.level8, f.level7, f.level6, f.level5, f.Level4, f.Level3, f.Level2, f.Level1) AS Level9,
		COALESCE(f.level10,f.level9, f.level8, f.level7, f.level6, f.level5, f.Level4, f.Level3, f.Level2, f.Level1) AS Level10,
		COALESCE(f.level11,f.level10,f.level9, f.level8, f.level7, f.level6, f.level5, f.Level4, f.Level3, f.Level2, f.Level1) AS Level11,
		COALESCE(f.level12,f.level11,f.level10,f.level9, f.level8, f.level7, f.level6, f.level5, f.Level4, f.Level3, f.Level2, f.Level1) AS Level12,
		COALESCE(f.level13,f.level12,f.level11,f.level10,f.level9, f.level8, f.level7, f.level6, f.level5, f.Level4, f.Level3, f.Level2, f.Level1) AS Level13,
		COALESCE(f.level14,f.level13,f.level12,f.level11,f.level10,f.level9, f.level8, f.level7, f.level6, f.level5, f.Level4, f.Level3, f.Level2, f.Level1) AS Level14,
		COALESCE(f.level15,f.level14,f.level13,f.level12,f.level11,f.level10,f.level9, f.level8, f.level7, f.level6, f.level5, f.Level4, f.Level3, f.Level2, f.Level1) AS Level15
FROM		(
			SELECT	p.id_KPI,
				p.Value,
				'Level' + CONVERT(VARCHAR(12), ROW_NUMBER() OVER (PARTITION BY p.id_KPI ORDER BY p.Level)) AS [Level]
			FROM	@temp AS s
			UNPIVOT	(
					[Value]
					FOR	[Level] IN (s.level_01, s.level_02, s.level_03, s.level_04, s.level_05, s.level_06, s.level_07, s.level_08, s.level_09, s.level_10, s.level_11, s.level_12, s.level_13, s.level_14, s.level_15)
				) AS p
		) AS y
PIVOT		(
			MAX(y.Value)
			FOR	y.[Level] IN ([Level1], [Level2], [Level3], [Level4], [Level5], [Level6], [Level7], [Level8], [Level9], [Level10], [Level11], [Level12], [Level13], [Level14], [Level15])
		) AS f
ORDER BY	f.id_KPI


select * from @temp_result

With cursor


declare  @id_KPI  int
	declare  @level_15      int
	declare  @level_14      int
	declare  @level_13      int
	declare  @level_12      int
	declare  @level_11      int
	declare  @level_10      int
	declare  @level_09       int
	declare  @level_08       int
	declare  @level_07       int
	declare  @level_06       int
	declare  @level_05       int
	declare  @level_04       int
	declare  @level_03       int
	declare  @level_02       int
	declare  @level_01       int

	declare  @level_15_display_order  int
	declare  @level_14_display_order  int
	declare  @level_13_display_order  int
	declare  @level_12_display_order  int
	declare  @level_11_display_order  int
	declare  @level_10_display_order  int
	declare  @level_09_display_order   int
	declare  @level_08_display_order   int
	declare  @level_07_display_order   int
	declare  @level_06_display_order   int
	declare  @level_05_display_order   int
	declare  @level_04_display_order   int
	declare  @level_03_display_order   int
	declare  @level_02_display_order   int
	declare  @level_01_display_order   int


	declare  @temp_result table
	(	id_KPI  int,
		level_01       int,
		level_02       int,
		level_03       int,
		level_04       int,
		level_05       int,
		level_06       int,
		level_07       int,
		level_08       int,
		level_09       int,
		level_10      int,
		level_11      int,
		level_12      int,
		level_13      int,
		level_14      int,
		level_15      int,

		level_01_display_order   int,
		level_02_display_order   int,
		level_03_display_order   int,
		level_04_display_order   int,
		level_05_display_order   int,
		level_06_display_order   int,
		level_07_display_order   int,
		level_08_display_order   int,
		level_09_display_order   int,
		level_10_display_order  int,
		level_11_display_order  int,
		level_12_display_order  int,
		level_13_display_order  int,
		level_14_display_order  int,
		level_15_display_order  int
	)



	declare  @temp  table
	(	id_KPI  int,
		level_01       int,
		level_02       int,
		level_03       int,
		level_04       int,
		level_05       int,
		level_06       int,
		level_07       int,
		level_08       int,
		level_09       int,
		level_10      int,
		level_11      int,
		level_12      int,
		level_13      int,
		level_14      int,
		level_15      int,

		level_01_display_order   int,
		level_02_display_order   int,
		level_03_display_order   int,
		level_04_display_order   int,
		level_05_display_order   int,
		level_06_display_order   int,
		level_07_display_order   int,
		level_08_display_order   int,
		level_09_display_order   int,
		level_10_display_order  int,
		level_11_display_order  int,
		level_12_display_order  int,
		level_13_display_order  int,
		level_14_display_order  int,
		level_15_display_order  int
	)




	insert into  @temp
		select
			L1.id_KPI,
			
			L15.id_KPI  as  level_01,
			L14.id_KPI  as  level_02,
			L13.id_KPI  as  level_03,
			L12.id_KPI  as  level_04,
			L11.id_KPI  as  level_05,
			L10.id_KPI  as  level_06,
			L9.id_KPI   as  level_07,
			L8.id_KPI   as  level_08,
			L7.id_KPI   as  level_09,
			l6.id_KPI   as  level_10,
			l5.id_KPI   as  level_11,
			l4.id_KPI   as  level_12,
			l3.id_KPI   as  level_13,
			l2.id_KPI   as  level_14,
			L1.id_KPI   as  level_15,

			L15.nn_display_order  as  level_01,
			L14.nn_display_order  as  level_02,
			L13.nn_display_order  as  level_03,
			L12.nn_display_order  as  level_04,
			L11.nn_display_order  as  level_05,
			L10.nn_display_order  as  level_06,
			L9.nn_display_order   as  level_07,
			L8.nn_display_order   as  level_08,
			L7.nn_display_order   as  level_09,
			l6.nn_display_order   as  level_10,
			l5.nn_display_order   as  level_11,
			l4.nn_display_order   as  level_12,
			l3.nn_display_order   as  level_13,
			l2.nn_display_order   as  level_14,
			L1.nn_display_order   as  level_15
		from
			KPI_hierarchy  L1   left join
			KPI_hierarchy  L2  on  L1.id_parent_KPI = l2.id_KPI		left join
			KPI_hierarchy  L3  on  L2.id_parent_KPI = l3.id_KPI		left join
			KPI_hierarchy  L4  on  L3.id_parent_KPI = l4.id_KPI		left join
			KPI_hierarchy  L5  on  L4.id_parent_KPI = l5.id_KPI		left join
			KPI_hierarchy  L6  on  L5.id_parent_KPI = l6.id_KPI		left join
			KPI_hierarchy  L7  on  L6.id_parent_KPI = l7.id_KPI		left join
			KPI_hierarchy  L8  on  L7.id_parent_KPI = l8.id_KPI		left join
			KPI_hierarchy  L9  on  L8.id_parent_KPI = l9.id_KPI		left join
			KPI_hierarchy  L10 on  L9.id_parent_KPI = l10.id_KPI	left join
			KPI_hierarchy  L11 on  L10.id_parent_KPI = l11.id_KPI	left join
			KPI_hierarchy  L12 on  L11.id_parent_KPI = l12.id_KPI	left join
			KPI_hierarchy  L13 on  L12.id_parent_KPI = l13.id_KPI	left join
			KPI_hierarchy  L14 on  L13.id_parent_KPI = l14.id_KPI	left join
			KPI_hierarchy  L15 on  L14.id_parent_KPI = l15.id_KPI


	declare csr cursor local  forward_only  static  read_only  for
		select
			id_KPI,
			level_01,   level_02,   level_03,   level_04,
			level_05,   level_06,   level_07,   level_08,
			level_09,   level_10,  level_11,  level_12,
			level_13,  level_14,  level_15,

			level_01_display_order,   level_02_display_order,   level_03_display_order,   level_04_display_order,
			level_05_display_order,   level_06_display_order,   level_07_display_order,   level_08_display_order,
			level_09_display_order,   level_10_display_order,  level_11_display_order,  level_12_display_order,
			level_13_display_order,  level_14_display_order,  level_15_display_order
		from  @temp
		

	open csr
	
	
	fetch next from csr  into
		@id_KPI,
		@level_01,   @level_02,   @level_03,   @level_04,
		@level_05,   @level_06,   @level_07,   @level_08,
		@level_09,   @level_10,  @level_11,  @level_12,
		@level_13,  @level_14,  @level_15,

		@level_01_display_order,   @level_02_display_order,   @level_03_display_order,   @level_04_display_order,
		@level_05_display_order,   @level_06_display_order,   @level_07_display_order,   @level_08_display_order,
		@level_09_display_order,   @level_10_display_order,  @level_11_display_order,  @level_12_display_order,
		@level_13_display_order,  @level_14_display_order,  @level_15_display_order

	

	while  @@fetch_status = 0  begin
		while  @level_01 is null  begin
			set  @level_01  = @level_02
			set  @level_02  = @level_03
			set  @level_03  = @level_04
			set  @level_04  = @level_05
			set  @level_05  = @level_06
			set  @level_06  = @level_07
			set  @level_07  = @level_08
			set  @level_08  = @level_09
			set  @level_09  = @level_10
			set  @level_10 = @level_11
			set  @level_11 = @level_12
			set  @level_12 = @level_13
			set  @level_13 = @level_14
			set  @level_14 = @level_15

			set  @level_01_display_order  = @level_02_display_order
			set  @level_02_display_order  = @level_03_display_order
			set  @level_03_display_order  = @level_04_display_order
			set  @level_04_display_order  = @level_05_display_order
			set  @level_05_display_order  = @level_06_display_order
			set  @level_06_display_order  = @level_07_display_order
			set  @level_07_display_order  = @level_08_display_order
			set  @level_08_display_order  = @level_09_display_order
			set  @level_09_display_order  = @level_10_display_order
			set  @level_10_display_order = @level_11_display_order
			set  @level_11_display_order = @level_12_display_order
			set  @level_12_display_order = @level_13_display_order
			set  @level_13_display_order = @level_14_display_order
			set  @level_14_display_order = @level_15_display_order
		end

		
		insert into @temp_result
		(	id_KPI,
			level_01,   level_02,   level_03,   level_04,
			level_05,   level_06,   level_07,   level_08,
			level_09,   level_10,  level_11,  level_12,
			level_13,  level_14,  level_15,

			level_01_display_order,   level_02_display_order,   level_03_display_order,   level_04_display_order,
			level_05_display_order,   level_06_display_order,   level_07_display_order,   level_08_display_order,
			level_09_display_order,   level_10_display_order,  level_11_display_order,  level_12_display_order,
			level_13_display_order,  level_14_display_order,  level_15_display_order
		)
		values
		(	@id_KPI,
			@level_01,   @level_02,   @level_03,   @level_04,
			@level_05,   @level_06,   @level_07,   @level_08,
			@level_09,   @level_10,  @level_11,  @level_12,
			@level_13,  @level_14,  @level_15,
	
			@level_01_display_order,   @level_02_display_order,   @level_03_display_order,   @level_04_display_order,
			@level_05_display_order,   @level_06_display_order,   @level_07_display_order,   @level_08_display_order,
			@level_09_display_order,   @level_10_display_order,  @level_11_display_order,  @level_12_display_order,
			@level_13_display_order,  @level_14_display_order,  @level_15_display_order
		)


		fetch next from csr  into
			@id_KPI,
			@level_01,   @level_02,   @level_03,   @level_04,
			@level_05,   @level_06,   @level_07,   @level_08,
			@level_09,   @level_10,  @level_11,  @level_12,
			@level_13,  @level_14,  @level_15,
	
			@level_01_display_order,   @level_02_display_order,   @level_03_display_order,   @level_04_display_order,
			@level_05_display_order,   @level_06_display_order,   @level_07_display_order,   @level_08_display_order,
			@level_09_display_order,   @level_10_display_order,  @level_11_display_order,  @level_12_display_order,
			@level_13_display_order,  @level_14_display_order,  @level_15_display_order
	end
	

	CLOSE       csr
	DEALLOCATE  csr

The time is the same (less than 1 sec) but I don't test with a bigger table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources