How do I best separate data into its own column

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)

How do I best separate data into its own column

Author

Topic

ifthenelsenull
Starting Member

3 Posts

Posted - 2011-11-10 : 21:51:24

SQL 2008

The basic outline: I have a table (observations) that contains an abbreviation with a data type of varchar but is distinct and a value which is a varchar but is just a string and can contain any number of values up to the field limit of 2000 characters. There is timelastchanged field that makes it unique to the table

The initial data set looks like:

Abbr Value Lastchanged
ABC123 Joe 20111104 00:00:00
ABC123 Joe|John|Mike 20111103 00:00:00
ABC456 Dan 20111104 00:00:00
ABCD12 Jane 20111104 00:00:00

What I want to do is to separate each Value row into its own column and only pull the most recent value
Abbr1 Abbr3 abbr4
Joe Dan Jane

My solution was to alias the table and self join it using the abbreviation value in the join to only get the values I wanted and then inserted that into a temp table which I would query later. I always know the abbreviations in advance so those aren't variable.

I figure there has to be a better way than joining a table to itself 10 times to get 10 distinct values I can use in a report.

My limitations include- no changes to the database, the most recent value can be NULL or an empty string and this is in the middle of a query not by itself.

Maybe I should pivot but I'm not sure how to pivot half a query. Maybe I should use a CTE but I'm not positive on how this works. Any advice I can get is appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 03:15:33

[code]
;With CTE (Abbr,Val,Lastchanged)
AS
(
SELECT t.Abbr,f.Val,t.Lastchanged
FROM table t
CROSS APPLY dbo.ParseValues(t.[Value],'|')f
)
SELECT [1],[2],[3],[4]
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY Abbr) AS Rn,Abbr,Val
FROM CTE c
INNER JOIN (SELECT Abbr,MAX(Lastchanged) AS MaxDate
FROM CTE
GROUP BY Abbr)c1
ON c1.Abbr = c.Abbr
AND c1.MaxDate = c.MaxDate
)m
PIVOT(MAX(Val) FOR Rn IN ([1],[2],[3],[4]))p
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources