Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I have a table:
IDX	MyText
1	Header1
2	BodyA
3	BodyB
4	Header2
5	BodyC
6	BodyD
7	BodyE
8	Header3
9	BodyF
10	BodyG
11	BodyH
 
What I want is:
IDX	MyHeader	MyText
2	Header1		BodyA
3	Header1		BodyB
5	Header2		BodyC
6	Header2		BodyD
7	Header2		BodyE
9	Header3		BodyF
10	Header3		BodyG
11	Header3		BodyH
I have tried:
SELECT IDX, MyText AS MyHeader
INTO #Temp1
FROM MyTable
WHERE LEFT(MyText, 7) = 'History'
 
SELECT
      P.IDX
    , (SELECT TOP(1) T.IDX, T.MyHeader FROM #Temp1 T WHERE T.IDX < P.IDX ORDER BY T.IDX DESC) AS MyHeader
    , P.MyText
FROM MyTable P
WHERE LEFT(P.MyText, 7) <> 'History'
To which I get the error:
Msg 116, Level 16, State 1, Line 3
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

 
Can someone point me in the correct direction?
Thanks
Posted 29-May-12 6:30am

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Found a solution at: stackoverflow SQL:How to fill empty cells with previous row value[^]
 
Took a little tweaking but got it to work.
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.150327.1 | Last Updated 29 May 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100