identifying column values using iterations

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)

identifying column values using iterations

Author

Topic

Neal44
Starting Member

17 Posts

Posted - 2010-04-12 : 05:50:50

Hi,

I have a temporary table that contains 2 fields (Name and Pos) that I need to group by. The 'Pos' values may change everytime I INSERT into this table:

Table1

Name	Pos
John	11
John	12
John	18
John	19
John	25
John	26
John	32
John	33
Emma	11
Emma	12
Emma	18
Emma	19
Emma	25
Emma	26
Emma	32
Emma	33
Paul	11
Paul	12
Paul	18
Paul	19
Paul	25
Paul	26
Paul	32
Paul	33

I need to perform 2 tasks on these fields. Firstly, I need add an indicator (Ind) on the 'Name' field for each occurence (where 1 = odd # AND 2 = even #). Then I need to group by each Name/Pos/Ind pairing.

(The Pos values could change everytime I import a file)

Name	Pos	Ind	Group
John	11	1	1
John	12	2	1
John	18	1	2
John	19	2	2
John	25	1	3
John	26	2	3
John	32	1	4
John	33	2	4
Emma	11	1	1
Emma	12	2	1
Emma	18	1	2
Emma	19	2	2
Emma	25	1	3
Emma	26	2	3
Emma	32	1	4
Emma	33	2	4
Paul	11	1	1
Paul	12	2	1
Paul	18	1	2
Paul	19	2	2
Paul	25	1	3
Paul	26	2	3
Paul	32	1	4
Paul	33	2	4

Any ideas please?

Thanks,

N

Neal

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-12 : 06:08:52

[code]
; with cte as
(
select *, row_no = row_number() over (partition by Name order by Pos)
from Table1
)
select Name, Pos,
Ind = case when row_no % 2 = 1 then 1 else 2 end,
[Group] = (row_no - 1) / 2 + 1
from cte
order by Name, Pos
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 06:14:40

[code]
SELECT Name,Pos,
CASE WHEN Seq%2=0 THEN 2 ELSE 1 END AS Ind,
((Seq-1)/2) + 1 AS [Group]
FROM
(
SELECT Name,Pos,ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Pos) AS Seq
FROM Table
)t
Order By Name,Pos
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-04-12 : 06:15:32


select name,pos,row_number()over(partition by name,rowid order by name,rowid) as Ind,dense_rank()over (order by name,rowid)as [Group] from
(
select *,pos-row_number()over(order by name)as rowid from yourtable

)t

PBUH

Neal44
Starting Member

17 Posts

Posted - 2010-04-12 : 06:22:13

WOW!

Thanks guys, that was amazingly prompt.

Neal

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-12 : 06:27:36

please verify and let me know if anything else you want...


SELECT	Name, Pos, Ind, 
		ROW_NUMBER() OVER(Partition by Name, Ind ORDER BY Name, Pos) [Group]
FROM	(
			SELECT	Name, Pos, CASE WHEN Rn%2 <> 0 THEN 1 ELSE 2 END Ind 
			FROM	(
						SELECT Name, Pos, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY (select 1) ) Rn
						FROM table1 
					) a
		) a
ORDER BY Name, Pos

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Neal44
Starting Member

17 Posts

Posted - 2010-04-12 : 08:01:51

I now need to insert my updated table into another SQL table.

Table1 is now:

Cust_Id	Name	Pos	Ind	Group	Val
1234	John	11	1	1	23
1234	John	12	2	1	23
1234	John	18	1	2	0
1234	John	19	2	2	2.4
1234	John	25	1	3	12.6
1234	John	26	2	3	23.45
1234	John	32	1	4	21.1
1234	John	33	2	4	14.5
1234	Emma	11	1	1	15.6
1234	Emma	12	2	1	15.32
1234	Emma	18	1	2	12.4
1234	Emma	19	2	2	11.6
1234	Emma	25	1	3	18.3
1234	Emma	26	2	3	14.4
1234	Emma	32	1	4	10.3
1234	Emma	33	2	4	17.4
1234	Paul	11	1	1	19.2
1234	Paul	12	2	1	17.3
1234	Paul	18	1	2	11.34
1234	Paul	19	2	2	10.3
1234	Paul	25	1	3	19.2
1234	Paul	26	2	3	19.7
1234	Paul	32	1	4	12.4
1234	Paul	33	2	4	14.5

I need to transfer the 'Val' data into specific columns based on the Name/Pos/Ind/Group grouping (the Cust_Id should always be the same).

I must insert into Table2 as:

Cust_Id	John_1	John_2	Emma_1	Emma_2	Paul_1	Paul_2
1234	23	23	15.6	15.32	19.2	17.3
1234	0	2.4	12.4	11.6	11.34	10.3
1234	12.6	23.45	18.3	14.4	19.2	19.7
1234	21.1	14.5	10.3	17.4	12.4	14.5

For Example:
A new row is inserted for each Group # count (i.e. 4 rows as this is MAX(Group))

John_1 is populated with data from Table1 where Ind = 1
John_2 is populated with data from Table1 where Ind = 2

I hope I have given you enough information.

Thanks,

N

Neal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 08:10:58

[code]SELECT Group,
MAX(CASE WHEN Name='John' AND Ind=1 THEN Val ELSE NULL END) AS John_1,
MAX(CASE WHEN Name='John' AND Ind=2 THEN Val ELSE NULL END) AS John_2,
MAX(CASE WHEN Name=Emma' AND Ind=1 THEN Val ELSE NULL END) AS Emma_1,
MAX(CASE WHEN Name='Emma' AND Ind=2 THEN Val ELSE NULL END) AS Emma_2,
MAX(CASE WHEN Name='Paul' AND Ind=1 THEN Val ELSE NULL END) AS Paul_1,
MAX(CASE WHEN Name='Paul' AND Ind=2 THEN Val ELSE NULL END) AS Paul_2
FROM Table
GROUP BY Group
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Neal44
Starting Member

17 Posts

Posted - 2010-04-13 : 04:03:54

Another success.

Thank YOU VERY much.

(and to everyone else who contributed).

This is a excellent site.

Neal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-13 : 06:56:37

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources