Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Transpose group per sequence
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
IF OBJECT_ID ('tempdb..#t') IS NOT NULL DROP TABLE #t IF OBJECT_ID ('tempdb..#m') IS NOT NULL DROP TABLE #m CREATE TABLE #t(FullName NVARCHAR(50),ID NVARCHAR(22),FamilyMember INT,Related NVARCHAR(20)) INSERT INTO #t VALUES ('FSDFE','23123312',1,'Household'), ('BVCB','34652',2,'Household'), ('JHGTYJ','765',2,'Spouse'), ('ZC','1236',3,'Household'), ('ER','0898',3,'Spouse'), ('VBCV','7567',3,'Daughter'), ('OUI','09878',1,'Household'), ('MJGG','56456',5,'Household'), ('ZDF','87687',5,'Spouse'), ('GFDR','75647',5,'Mother'), ('JKTY','908768',5,'Daughter'), ('OUI','24',5,'Son') ;with a as ( select *,row_number()over(partition by Related order by seq) as fid from ( select *,row_number()over(order by getdate()) as seq from #t ) as t ) select a.FullName,a.seq,a.FamilyMember,a.Related,a.id,m.fid,row_number()over(partition by m.fid order by a.seq) as mid into #m from a cross apply(select top 1 fid from a as ma where ma.Related='Household' and ma.seq<=a.seq order by id desc) m --order by id declare @sql nvarchar(max) select @sql=isnull(@sql+',','') +N'max(case when mid='+ltrim(sv.number)+N' then FullName else '''' end) as FullName'+ltrim(sv.number) +N',max(case when mid='+ltrim(sv.number)+N' then seq else '''' end) as seq'+ltrim(sv.number) +N',max(case when mid='+ltrim(sv.number)+N' then Related else '''' end) as Related'+ltrim(sv.number) from master.dbo.spt_values as sv where sv.type='P' and sv.number between 1 and (select max(FamilyMember) from #m) set @sql=N'select fid,FamilyMember,'+@sql+N' from #m as m group by fid,FamilyMember order by fid' exec(@sql)
View schema
Execution time: 0,03 sec, rows selected: 11, rows affected: 12, absolute service time: 0,19 sec
edit mode
|
history
|
discussion
fid
FamilyMember
FullName1
seq1
Related1
FullName2
seq2
Related2
FullName3
seq3
Related3
FullName4
seq4
Related4
FullName5
seq5
Related5
1
1
1
FSDFE
1
Household
0
0
0
0
2
2
1
0
0
0
0
0
3
2
2
BVCB
2
Household
JHGTYJ
3
Spouse
0
0
0
4
2
3
0
0
ZC
4
Household
ER
5
Spouse
VBCV
6
Daughter
5
5
5
MJGG
8
Household
ZDF
9
Spouse
GFDR
10
Mother
JKTY
11
Daughter
OUI
12
Son