How to display row value as column value in SQL Server (only one column rows value should be displayed as multiple columns) -


using join of parent , child tables getting results this

  select        a.id, a.pname, b.childname            parentinfo    right join        childinfo b on a.id = b.pid

output:

   id   pname       childname    --------------------------     1   parent1     p1child1     1   parent1     p1child2     1   parent1     p1child3     2   parent2     p2child1     2   parent2     p2child2     3   parent2     p3child1     3   parent3     p3child2     3   parent3     p3child3     3   parent3     p3child4     4   parent4     p4child1     4   parent4     p4child2     4   parent4     p4child3

but children should displayed columns, , 1 parent should displayed once.

and there can numbers of children.

i want display results this:

id  pname     child1    child2     child3     child4  ------------------------------------------------------ 1   parent1   p1child1  p1child2   p1child3 2   parent2   p2child1  p2child2    3   parent3   p3child1  p3child2   p3child3    p3child4 4   parent4   p4child1  p4child2   p4child3

how achieve this? using pivot table or other means?

this query converted rows columns 

declare @cols nvarchar(max),         @query nvarchar(max)  select @cols = stuff((select ',' + quotename(childname)                                                   (select *                             parentinfo                             right join childinfo b on a.id = b.pid) tt                       group childname, id                       order id                       xml path(''), type).value('.', 'nvarchar(max)')      ,1,1,'')      set @query = 'select ' + @cols + '           (             select value, columnname             yourtable         ) x         pivot          (             max(value)             columnname in (' + @cols + ')         ) p '       execute(@query)

here 1 approach using dynamic crosstab:

sql fiddle

generate sample data

use tempdb; create table yourtable(     id          int,     pname       varchar(20),     childname   varchar(20) ) insert yourtable values (1, 'parent1', 'p1child1'),  (1, 'parent1', 'p1child2'),  (1, 'parent1', 'p1child3'),  (2, 'parent2', 'p2child1'),  (2, 'parent2', 'p2child2'),  (3, 'parent3', 'p3child1'),  (3, 'parent3', 'p3child2'),  (3, 'parent3', 'p3child3'),  (3, 'parent3', 'p3child4'),  (4, 'parent4', 'p4child1'),  (4, 'parent4', 'p4child2'),  (4, 'parent4', 'p4child3');

dynamic crosstab

declare @maxnochildren int declare @sql1 varchar(4000) = '' declare @sql2 varchar(4000) = '' declare @sql3 varchar(4000) = ''  select top 1 @maxnochildren = count(*) yourtable group id order count(*) desc  select @sql1 =  'select     id     ,pname '  select @sql2 = @sql2 + '   ,max(case when rn = ' + convert(varchar(5), n) + ' childname end) ' + quotename('child' + convert(varchar(5), n)) + char(10) from(     select top(@maxnochildren)         row_number() over(order (select null))     sys.columns     --cross join sys.columns b )t(n) order n  select @sql3 = 'from(     select *,         rn = row_number() over(partition id order (select null))     yourtable )t group id, pname order id'  print(@sql1 + @sql2 + @sql3) exec (@sql1 + @sql2 + @sql3)

result

| id |   pname |   child1 |   child2 |   child3 |   child4 | |----|---------|----------|----------|----------|----------| |  1 | parent1 | p1child1 | p1child2 | p1child3 |   (null) | |  2 | parent2 | p2child1 | p2child2 |   (null) |   (null) | |  3 | parent3 | p3child1 | p3child2 | p3child3 | p3child4 | |  4 | parent4 | p4child1 | p4child2 | p4child3 |   (null) |

-

Comments

Post a Comment

Popular posts from this blog

OpenCV OpenCL: Convert Mat to Bitmap in JNI Layer for Android -

there several posts converting mat bitmap using utils.mattobitmap() function. i'm assuming function can called in java layer after importing utils class. i want transfer data memory address pointed uint32_t* bmpcontent; in code below. jniexport void jnicall java_com_nod_nodcv_nodcvactivity_runfilter( jnienv *env, jclass clazz, jobject outbmp, jbytearray indata, jint width, jint height, jint choice, jint filter) { int outsz = width*height; int insz = outsz + outsz/2; androidbitmapinfo bmpinfo; if (androidbitmap_getinfo(env, outbmp, &bmpinfo) < 0) { throwjavaexception(env,"gaussianblur","error retrieving bitmap meta data"); return; } if (bmpinfo.format != android_bitmap_format_rgba_8888) { throwjavaexception(env,"gaussianblur","expecting rgba_8888 format"); return; } uint32_t* bmpcontent; if (androidbitmap_lockpixels(env, outbmp,(void**)&bmpcontent)...
Read more

android - org.xmlpull.v1.XmlPullParserException: expected: START_TAG {http://schemas.xmlsoap.org/soap/envelope/}Envelope -

i trying hit soap api in application login purpose , getting above mentioned error , can 1 tell me going wrong , first time soap apis. here code hit soap :- vector<object> args = new vector<object>(); hashtable<string, object> hashtable = new hashtable<string, object>(); hashtable.put("email", "mss.siddhart28@gmail.com"); hashtable.put("password", "siddharth"); args.add(hashtable); soapserializationenvelope envelope = new soapserializationenvelope( soapenvelope.ver11); envelope.dotnet = true; envelope.xsd = soapserializationenvelope.xsd; envelope.enc = soapserializationenvelope.enc; (new marshalhashtable()).register(envelope); httptransportse androidhttptransport = new httptransportse( "http://tempuri.org/", 15000); androidhttptransport.debug = true; string result = ""; try { soapobject request = new soapobject("http:...
Read more

python - How to remove the Xframe Options header in django? -

i have made page has iframe . inside iframe want show multiple different links article facebook, or news, or youtube video or other possible url. but, due xframe header, unable so. referred following link: https://docs.djangoproject.com/en/1.8/ref/clickjacking/ , django xframeoptionsmiddleware (x-frame-options) - allow iframe client ip but didn't help. my settings.py file's middleware_classes is: middleware_classes = ( 'django.contrib.sessions.middleware.sessionmiddleware', 'django.middleware.common.commonmiddleware', 'django.middleware.csrf.csrfviewmiddleware', 'django.contrib.auth.middleware.authenticationmiddleware', 'django.contrib.auth.middleware.sessionauthenticationmiddleware', 'django.contrib.messages.middleware.messagemiddleware', 'django.middleware.clickjacking.xframeoptionsmiddleware', ) from http://django-secure.readthedocs.org/en/latest/middleware.html , found using ...
Read more