Interested Data Query Needed - 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 2012 Forums

Transact-SQL (2012)

Interested Data Query Needed

Author

Topic

d87c
Starting Member

2 Posts

Posted - 2014-04-04 : 12:27:47

For Example tblStudent


SID    Name    Grade
1
2
3
4
       David   
       Kim    
       Jessica
       Dale
                12
                11
                11
                10 

Somehow I need to transform to like

SID    Name      Grade
1      David     12
2      Kim       11
3      Jessica   11
4      Dale      10

I am fine with either an update statement to its table or insert to a new table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-04 : 16:27:48

I don't understand your example data. Show us the insert statements for that data as it doesn't make sense.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

d87c
Starting Member

2 Posts

Posted - 2014-04-04 : 17:04:44

CREATE TABLE table_name
(
ID INT,
SNUM VARCHAR(40),
NAME VARCHAR(40),
GRADE VARCHAR(40)
);

INSERT INTO table_name (ID,SNUM,NAME,GRADE)
VALUES (1,'ST01','','');

INSERT INTO table_name (ID,SNUM,NAME,GRADE)
VALUES (2,'ST02','','');

INSERT INTO table_name (ID,SNUM,NAME,GRADE)
VALUES (3,'ST03','','');

INSERT INTO table_name (ID,SNUM,NAME,GRADE)
VALUES (4,'ST04','','');

INSERT INTO table_name (ID,SNUM,NAME,GRADE)
VALUES (5,'','Simon','');

INSERT INTO table_name (ID,SNUM,NAME,GRADE)
VALUES (6,'','Kim','');

INSERT INTO table_name (ID,SNUM,NAME,GRADE)
VALUES (7,'','Jessica','');

INSERT INTO table_name (ID,SNUM,NAME,GRADE)
VALUES (8,'','Dale','');

INSERT INTO table_name (ID,SNUM,NAME,GRADE)
VALUES (9,'','','12');

INSERT INTO table_name (ID,SNUM,NAME,GRADE)
VALUES (10,'','','11');

INSERT INTO table_name (ID,SNUM,NAME,GRADE)
VALUES (11,'','','11');

INSERT INTO table_name (ID,SNUM,NAME,GRADE)
VALUES (12,'','','10');

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-05 : 04:20:57

[code]
;with cteSNume
AS
(select sNum
,ROW_NUMBER() OVER(ORDER BY ID) as rnSnum
from table_name
where Snum<>'')
,cteName
AS
(select Name
,ROW_NUMBER()OVER(ORDER BY ID) as rnName
from table_name
where Name<>'')
,cteGrade
AS
(select Grade
,ROW_NUMBER()OVER(ORDER BY ID) as rnGrade
from table_name
where Grade<>'')

select
A.SNum
,B.Name
,C.Grade
from
cteSNume as A
LEFT JOIN cteName as B ON A.rnSnum=B.rnName
LEFT JOIN cteGrade as C ON A.rnSnum=C.rnGrade
[/code]

output
[code]
SNum Name Grade
ST01 Simon 12
ST02 Kim 11
ST03 Jessica 11
ST04 Dale 10
[/code]

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources