New View Data - 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 2008 Forums

Transact-SQL (2008)

New View Data

Author

Topic

buckeyeJane
Starting Member

11 Posts

Posted - 2012-03-02 : 09:20:57

I need to create a view that will take the following data:


ID      DocID   IndxID  IndexValue
2	7	34	FirstName, LastName	
3	7	33	2003003996	
5	10	34	FirstName, LastName	
6	10	33	2003004419	
8	13	34	FirstName, LastName	
9	13	33	2003000942	
11	16	34	Firstname, LastName
12	16	33	2003002823	
14	19	34	FirstName, LastName
15	19	33	2003004440	
17	22	34	FirstName, LastName

The above data needs to be created and reformated into a view as follows:

ID      DocID   IndxID  DebtorName	IndexID	DebtorID
2	7	34	SMITH, MARY	        33	2003003999	
5	10	34	BLACK, LINDA		33	2003004419

What is the best way to reformat this view? Is a cursor the best process or a subquery? Any suggestions and advice is greatly appreaciate.

Thanks,
BuckeyeJane

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-02 : 10:03:59

[code]DECLARE @Sample TABLE
(
ID INT,
DocID INT,
IndxID INT,
IndexValue VARCHAR(MAX)
)

INSERT @Sample
VALUES ( 2, 7, 34, 'FirstName, LastName'),
( 3, 7, 33, '2003003996'),
( 5, 10, 34, 'FirstName, LastName'),
( 6, 10, 33, '2003004419'),
( 8, 13, 34, 'FirstName, LastName'),
( 9, 13, 33, '2003000942'),
(11, 16, 34, 'Firstname, LastName'),
(12, 16, 33, '2003002823'),
(14, 19, 34, 'FirstName, LastName'),
(15, 19, 33, '2003004440'),
(17, 22, 34, 'FirstName, LastName')

-- SwePeso
SELECT l.ID,
l.DocID,
l.IndxID,
l.IndexValue AS DebtorName,
r.IndxID AS IndexID,
r.IndexValue AS DebtorID
FROM @Sample AS l
INNER JOIN @Sample AS r ON r.DocID = l.DocID
AND r.IndxID = 33
WHERE l.IndxID = 34[/code]

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-02 : 10:08:58

Can you remodel the original dataset? That's a horrible design

What does IndxID column represent? Is 34 always the debtorName? and 33 *always* the debrorID?

How is that enforced?

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

buckeyeJane
Starting Member

11 Posts

Posted - 2012-03-02 : 11:25:46

Charlie - it's not my design and cannot be changed... :(

SwePeso - Thanks for the example and code. Since this is going to be in a View and we would need for it to update without having to refresh, the With SCHEMABINDING will not work because it cannot reference itself. Is there another option to not have to refresh the view manually?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources