I have this table:
| F1 | F2 |
|---|---|
| Header1 | NULL |
| abc | 1 |
| def | 2 |
| ghi | 3 |
| Header2 | NULL |
| jkl | 1 |
| mno | 2 |
| pqr | 3 |
For every NULL in F2, it will be the start of a new grouping. How would I get the above data to look like this:
| F1 | F2 | F3 |
|---|---|---|
| Header1 | NULL | Header1 |
| abc | 1 | Header1 |
| def | 2 | Header1 |
| ghi | 3 | Header1 |
| Header2 | NULL | Header2 |
| jkl | 1 | Header2 |
| mno | 2 | Header2 |
| pqr | 3 | Header2 |
how does one correlate abc to be subData of Header1? just because it just so happens it is sorted right below Header1 does not make it a child of Header1
Smells like there is underlying issue you need to resolve.
This was the way how the vendor setup their data for the current form. Yes, it is bad design on their side but there is nothing that I can do about. They did the other forms correctly where there is a parent child relationship.
Anyway, there has to be a way to do the above.
I forgot to add, there is a sort order field too.
I was able to do it with something like this but not sure if this is the best way of doing it.
IF OBJECT_ID('tempdb..#TEMPTable') IS NOT NULL
BEGIN
DROP TABLE #TEMPTable
END
SELECT
Order
, F1
INTO #TEMPTable
FROM #TEMPTable_Objects
AND F2 IS NULL
------------------------------------------------------------
SELECT
(SELECT TOP 1 aT1.F1 FROM #TEMPTable aT1
WHERE aT1.Order <= aT22.Order
ORDER BY aT1.Order DESC) AS Test
FROMhi i took a stab at this
hope it helps
USE tempdb
go
DROP TABLE data
go
CREATE TABLE data
(
f1 VARCHAR(100),
f2 INT NULL
)
go
INSERT INTO data
SELECT 'Header1',
NULL
INSERT INTO data
SELECT 'abc',
1
INSERT INTO data
SELECT 'def',
2
INSERT INTO data
SELECT 'ghi',
3
INSERT INTO data
SELECT 'Header2',
NULL
INSERT INTO data
SELECT 'jkl',
1
INSERT INTO data
SELECT 'mno',
2
INSERT INTO data
SELECT 'pqr',
3
go
SELECT *
FROM data
go
USE tempdb
go
;
WITH cte
AS (SELECT Row_number()
OVER (
ORDER BY (SELECT NULL)) AS rn,
*
FROM data),
reccte
AS (SELECT 1 AS grp,
*
FROM cte
WHERE rn = 1
UNION ALL
SELECT CASE
WHEN a.f2 IS NULL THEN b.grp + 1
ELSE b.grp
END AS grp,
a.*
FROM cte a
JOIN reccte b
ON a.rn = b.rn + 1)
SELECT *
FROM reccte
go
Have you tried lag
hi mike
dont know if you are talking to me
but if you are
i dont have sql server 2012
i am on sql server 2008
:
I'm not sure which version of SQL the OP is using. If they are using SQL 2012 or higher, then they could probably use Lag
i am trying to think how lead or lag can be used
how will it be used ?
probably
dense rank .. partition by check current column if null to lead lag ???
I used Lead instead of Lag
Declare @t table (id int identity(1,1),
F1 varchar(10),
F2 int)
declare @MaxID int
insert into @t
values
('Header1',null),
('abc',1),
('def',2),
('ghi',3),
('Header2',null),
('jkl',1),
('mno',3),
('pqr',2)
select @MaxID = @@RowCount
;with cte as (select ID, F1
from @t
where F2 is null),
cte1 as (select c.F1, c.ID, IsNull(lead(c.ID,1) over (order by c.id) - 1,@MaxID) ID2
from cte c)
select t.F1, F2, c.F1
from @t t
join cte1 c
on t.ID between c.ID and c.ID2gotcha
thanks mike
Hi Harish and Mike,
I want to thank both of you. I have never used Lead or Lag before and now I just learned something knew today. I will research into both and thank you for providing your sample Mike.
I am on SQL Server 2014.
Thank You again.
Hi babyAqua
I think you are trying to learn tsql
So along the same line
Learning How to Learn.. concept
You tube videos lots of them are there
How to articles lots there
Google search
If you are interested in the below thing s
Hope you find it useful
hi BabyAqua
So along the same line
Learning How to Learn.. concept
if you are interested in this topic
We could take this OFFLINE
my research notes etc etc etc etc
I am delivering a deadline this week but will definitely take you up on your offer Harish as I would like to understand more from your approach. Thank You sir and your kindness!
I will reach out to you sometime next week.
sounds good
