1

Topic: Grouping without null

Tell please how to group so that all data were tightened upwards without null?
That is that in all null there was below, and all data above?
Thankful in advance.

2

Re: Grouping without null

The grouping, instead of sorting is precisely necessary?

3

Re: Grouping without null

Akina;
It is possible also sorting, but how?

4

Re: Grouping without null

vah wrote:

Akina;
It is possible also sorting, but how?

sorting do by spelling ORDER BY in the end of request.
It is possible to sort by expression:

case when [111] is null then 1 else 0 end

5

Re: Grouping without null

alexeyvg;
Like too most it turns out...

6

Re: Grouping without null

vah;
And without like?
Though so

ORDER BY case when [111] is null then 1 else 0 end

Though

ORDER BY ISNULL (111, ' zzz ')

7

Re: Grouping without null

vah wrote:

alexeyvg;
Like too most it turns out...

Is not present.
result, or though the code show that you there wrote.

8

Re: Grouping without null

alexeyvg;

declare @sql nvarchar (max) ="
set @sql = @sql + ' SELECT ' + @var + ' FROM (' + char (10)
set @sql = @sql + ' select distinct a.id, b.id idm, b.parentId, b.name from ' + char (10)
set @sql = @sql + ' #tmp_struct an inner join #tmp_struct b on a.id=b.parentId ' + char (10)
set @sql = @sql + ' where a.lvl=1 ' + char (10)
set @sql = @sql + ') AS pr ' + char (10)
set @sql = @sql + ' PIVOT (max (name) FOR [id] in (' + @var + ')) AS pvt'
exec (@sql)

9

Re: Grouping without null

vah wrote:

alexeyvg;

declare @sql nvarchar (max) ="
set @sql = @sql + ' SELECT ' + @var + ' FROM (' + char (10)
set @sql = @sql + ' select distinct a.id, b.id idm, b.parentId, b.name from ' + char (10)
set @sql = @sql + ' #tmp_struct an inner join #tmp_struct b on a.id=b.parentId ' + char (10)
set @sql = @sql + ' where a.lvl=1 ' + char (10)
set @sql = @sql + ') AS pr ' + char (10)
set @sql = @sql + ' PIVOT (max (name) FOR [id] in (' + @var + ')) AS pvt'
exec (@sql)

did not see grouping or sorting. From a word - absolutely...

10

Re: Grouping without null

It seems to me we it not so understood, probably he wants that was type:
PR, it is feeble?, , the Adviser, is single line, and further in the same spirit.

11

Re: Grouping without null

aleksrov;
That's it, it is difficult to explain

12

Re: Grouping without null

vah;
Whether that, it is simple in each column to shift all data upwards?
Then you need to spread out sampling to tables-columns. To make it is so much tables, how many columns you wish to see in total sampling. Each turned out table-column to renumber with the help row_number. Further   to connect all these fields-tables in one total data set....

13

Re: Grouping without null

vah wrote:

aleksrov;
That's it, it is difficult to explain

It is difficult to explain on fingers? - Give the data and draw an output which of this data should turn out.

14

Re: Grouping without null

Kind  - Eh;
It agree smile

DECLARE @a TABLE
(
[111] VARCHAR (5);
[222] VARCHAR (5);
[333] VARCHAR (5)
)
INSERT INTO @a VALUES
(NULL, NULL, ' LLL ');
(' AAAA ', NULL, NULL);
(NULL, ' BBB ', ' CCC ');
(' XXXX ', NULL, ' NNN ');
(' DDDD ', NULL, NULL);
(NULL, NULL, ' III ')
;WITH an as
(
SELECT
[r] = ROW_NUMBER () OVER (ORDER BY 1/0);
[a] = [111]
FROM @a
WHERE [111] IS NOT NULL
), b as
(
SELECT
[r] = ROW_NUMBER () OVER (ORDER BY 1/0);
[a] = [222]
FROM @a
WHERE [222] IS NOT NULL
), c as
(
SELECT
[r] = ROW_NUMBER () OVER (ORDER BY 1/0);
[a] = [333]
FROM @a
WHERE [333] IS NOT NULL
)
SELECT
a.a;
b.a;
c.a
FROM a
FULL JOIN b
ON
a.r = b.r
FULL JOIN c
ON
c.r = a.r

15

Re: Grouping without null

TaPaK;
Thanks!

16

Re: Grouping without null

My variant "with structure saving":

DECLARE @a TABLE
(
[111] VARCHAR (5);
[222] VARCHAR (5);
[333] VARCHAR (5)
)
INSERT INTO @a VALUES
(' AAAA ', NULL, NULL);
(NULL, NULL, ' FFF ');
(NULL, ' EEE ', NULL);
(NULL, ' BBB ', NULL);
(NULL, NULL, ' CCC ');
(NULL, NULL, ' LLL ');
(' XXXX ', NULL, NULL);
(' ZZZZ ', NULL, NULL);
(NULL, NULL, ' NNN ');
(' DDDD ', NULL, NULL);
(NULL, NULL, ' III ');
with
A as
(Select *, row_number () over (Order by (select 1)) as rn From @a);
B as
(Select *, count ([111]) over (Order by rn) as ID_111 From A);
C as
(Select *;
count ([222]) over (partition by ID_111 Order by rn) as ID_222;
max ([111]) over (partition by ID_111) as [_111]
From B);
D as
(Select *;
count ([333]) over (partition by ID_111, ID_222 Order by rn) as ID_333;
max ([222]) over (partition by ID_111, ID_222) as [_222]
From A C);
E as
(Select *;
max ([333]) over (partition by ID_111, ID_222, ID_333) as [_333];
max (ID_222) over (partition by ID_111) as Count_222;
max (ID_333) over (partition by ID_111, ID_222) as Count_333
From D)
Select [_111], [_222], [_333] From E
Where not (Count_222> 0 and ID_222 = 0)
and not (Count_333> 0 and ID_333 = 0)

Result:
_111, _222, _333
AAAA, EEE, <NULL>
AAAA, BBB, CCC
AAAA, BBB, LLL
XXXX, <NULL>, <NULL>
ZZZZ, <NULL>, NNN
DDDD, <NULL>, III

17

Re: Grouping without null

TaPaK;
If values in a column "B" more than in "A" the result will be incorrect. More correctly:

SELECT
a.a;
b.a;
c.a
FROM a
FULL JOIN b
ON
a.r = b.r
FULL JOIN c
ON
-----Correction:
c.r = isnull (a.r, b.r)

18

Re: Grouping without null

Kopelly;
Also that wrong, at us Full Join so will be in A will be Null, and in B .
And your request of discharge as simple things to make difficult smile

19

Re: Grouping without null

In my variant a wrong condition. More correctly so:

From E
Where not (Count_222> 0 and ID_222 = 0 and Count_333=0)
and not (Count_333> 0 and ID_333 = 0)

20

Re: Grouping without null

aleksrov;
Yes  you to criticize....
The person the second day at a forum. wink
Let writes any decisions. At it an interesting foreshortening in the approach to the decision of hackneyed tasks of a forum.
It is necessary to estimate height of flight of its thought.
Fresh blood it is always good, can that and for ourselves we look smile
..
I quite seriously, without sarcasm...

21

Re: Grouping without null

aleksrov;
At

INSERT INTO @a VALUES
(NULL, NULL, ' LLL ');
(' AAAA ', NULL, NULL);
(NULL, ' BBB ', ' CCC ');
(' XXXX ', NULL, ' NNN ');
(' DDDD ', NULL, NULL);
(NULL, NULL, ' III ');
(NULL, ' QQQ ', NULL);
(NULL, ' WWW ', NULL);
(NULL, ' EEE ', NULL);
(NULL, ' RRR ', NULL)

We receive:
a, a, a
AAAA, BBB, LLL
XXXX, QQQ, CCC
DDDD, WWW, NNN
<NULL>, EEE, <NULL>
<NULL>, RRR, <NULL>
<NULL>, <NULL>, III
Instead of:
a, a, a
AAAA, BBB, LLL
XXXX, QQQ, CCC
DDDD, WWW, NNN
<NULL>, EEE, III
<NULL>, RRR, <NULL>