How can I create view that consolidates attributes

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

General SQL Server Forums

New to SQL Server Programming

How can I create view that consolidates attributes

Author

Topic

quantoidboy
Starting Member

1 Post

Posted - 2008-06-06 : 19:33:42

This is kind of tricky. I have a table that is structured like this (where the StaffID is the primary key):

StaffID, Date1, Amount1, Date2, Amount2, Date3, Amount3
123, 1/1/08, 100, 2/1/08, 200, 3/1/08, 300

You can already see that whoever made this table wasn't thinking prudently. Anyway, I would like to make the table look like this:

StaffID, Date, Amount
123, 1/1/08, 100
123 2/1/08, 200
123 3/1/08, 300

Where the StaffID and Date are concatenated keys.

Is there a way to either create a View or create a Query Table that converts the data like that for me? I need the new view/query to update when the original table updates, so a new table isn't an option.

Thanks ahead of time!

Reuben

ranganath
Posting Yak Master

209 Posts

Posted - 2008-06-07 : 03:11:29

Hi,

try with this

DEclare @T Table
(
StaffID Int, Date1 varchar(100), Amount1 Int,
Date2 varchar(100), Amount2 Int, Date3 varchar(100), Amount3 Int
)
Insert into @T
Select 123, '1/1/08', 100, '2/1/08', 200, '3/1/08', 300 union all
Select 124, '1/1/08', 1000, '2/1/08', 2000, '3/1/08', 3000

Select Staffid, Date1 + ' - ' + CAST(Amount1 as varchar(100)) +'#'+ Date2 + ' - ' + CAST(Amount2 as varchar(100))+'#'+ Date3 + ' - ' + CAST(Amount3 as varchar(100)) as 'Data' into #T From @T

DECLARE @delimiter NVARCHAR(5)
SELECT @delimiter = '#'

Declare @Temp Table ( id int, Val varchar(100))
insert into @Temp

SELECT s.Staffid,
SUBSTRING(s.data, v.Number - 1,
COALESCE(NULLIF(CHARINDEX('#', s.data, v.Number), 0), LEN(s.data) + 1) - v.Number + 1) AS value
FROM #T AS s
INNER JOIN master..spt_values AS v ON v.Type = 'p'
WHERE SUBSTRING('#_' + s.data, v.Number, 1) = '#'

Select id, Substring(Val,1,charindex ('-', val)-1) as Date, Substring(Val,9,len(val)) as Amount From @Temp

drop table #T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-07 : 04:10:29

or even this

SELECT StaffID,Date1 AS Date,Amount1 AS Amount
FROM Table

UNION ALL

SELECT StaffID,Date2,Amount2
FROM Table

UNION ALL

SELECT StaffID,Date3,Amount3
FROM Table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources