.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Transposing data into columns

Posted By:      Posted Date: August 29, 2010    Points: 0   Category :Sql Server
Hello All Consider the follwing dataset/layout: Person ID Question Answer P1 Q1 Q1 Answer P1 Q2 Q2 Answer P1 Q3 Q3 Answer P2 Q1 Q1 Answer P2 Q2 Q2 Answer P2 Q3 Q3 Answer I'd like to be able to have a list of 'Persons' with their associated questions and answers like this: Person ID Q1 Q2 Q3 P1 Q1 Answer Q2 Answer Q3 Answer P2 Q1 Answer Q2 Answer Q3 Answer (The questions will remain the same for each person, but will have different answers). There will be other fields in there too (such as a date and various other bits of demographics) but if I can nail the methodology, I can expand it to include. I've done this kind of thing in the past, but can never reproduce the code/methodology off the top of my head! Any tips would be great... Cheers Leeroyster

View Complete Post

More Related Resource Links

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Entity Data Model and database view returning the same columns as there are in a table


When adding a stored procedure into the Entity Data Model I can select whether the procedure returns a scalar, a (new) complex type or one of the entity types I already defined. 

How do I do something similar for a view?

I mean assuming I have a view like this

CREATE VIEW FilteredFoos as SELECT Foo.* FROM Foo join ... WHERE ...

(that is a view that implements some involved filtering, but returns all columns from one table) how do I add it to the project so that I can use the entity set, but get the Foo objects, not some new FilteredFoo objects.

var foos = myDB.FilteredFoos.Include("Bar").ToList();

foreach (Foo foo in foos) { ...

Thanks, Jenda

How to create scrolling table for columns in a SharePoint Designer Data View


I have a fairly complex SharePoint Designer 2007 Data View they have added two more requirements.

1. Have the columns on the right side be able to scroll left to right as there is a large number of columns, while the first 3 columns on the left side stay locked in place.  This is only one list not two, and because of some other requirements I have it must remain to be just one list.

2. Then they also need to have scrolling up and down of the entire list with the headers locked in place.

The first requirement is more important than the second, but preferrably I need to do both.

I am new to XSL so not familar with the syntax. Is this something I can do on a custom Data View? Does anyone have any examples of how?

I have a drawing that show how it needs to look but I don't see an option here to upload a graphic.

So here is a link to my graphic example uploaded to my blog area: http://lindachapman.blogspot.com/  or just the image here: http://4.bp.blogspot.com/_aB01ue__NvQ/TGL42MOvrGI/AAAAAAAAAA4/c3xP4uCy4BU/s1600/Scrolling.png

I failed to mention that 3/4 of the columns are HTML Calculated fields wh

Library - Fill Columns with data from email

I have an inbound email enabled Library.  The Library has several columns created for it.  I would like to be able to send an email into the Library and have these columns filled from information in the email.  Is this possible using WSS 3.0?  What are the basic steps?  If I understand SharePoint well enough, I'm guessing is that I have to include the columns as Metadata in the body of the email and then assign this Metadata to the appropriate Library columns when new email is uploaded to the Library.

how to prepare WHERE clause in vertical columns data

here the sample table X with datatype againt each data..how to prepare below WHERE clause from table X, WHERE   S_NAME = 'AYAZ' AND S_ROLL = 10 AND S_MARKS = 99 AND S_SCHOOL = 'ZINQ'     and table X is, CREATE TABLE X (UID INT IDENTITY(1,1), COL_N VARCHAR(50), COL_V VARCHAR(50), COL_D VARCHAR(50)) INSERT INTO X VALUES ('S_NAME', 'AYAZ', 'VARCHAR(50)') INSERT INTO X VALUES ('S_ROLL', '10', 'DECIMAL(15, 0)') INSERT INTO X VALUES ('S_MARKS', '99', 'INT') INSERT INTO X VALUES ('S_SCHOOL', 'ZINQ', 'VARCHAR(100)') --SELECT * FROM X  


the database result is as follows DATE     code   class1 class2  class3 1/8/10     M1     10       20     30     3/8/10     V2      20       30     10      1/8/10      H1      1           2      3         7/8/10     ! ! 8/8/10     !M2    5      5      5       3/8/10     B2      4      4     4       1/8/10     V1      3      7     3       THE OUTPUT SHOULD BE AS FOLLOWS:- DATE           M1                          M2           

Comparing columns with NULL values--Merge says unmatched when data is matched.

I found the article below describing this same issue with the Oracle merge statement.  It also described a work-around:    http://searchoracle.techtarget.com/tip/Merging-datasets-with-NULL-values I have been unable to find a solution to this issue for SQL Server 2008.  I am trying to do basic ETL from Staging table to a Type II dimension table in a Data Warehouse.  I am using the Merge statement with the Unique key minus the modification date as my merge key list.  I have a handful of columns (in both Staging and the DW) containing nulls in my merge key list.  I have tried the ISNULL function and the ANSI_NULL db option (OFF) with no success.  When I put zeros in the null columns, it works as expected.  I am about to try separate INSERT and UPDATE statements, but I fear that the NULL comparison issue will bite me there with the JOIN statement.  Has anyone else experienced this?  NULL values are valid in our Data Warehouse.  How can I make T-SQL handle them? Thank you for your help, Jesse

How to read an Excel file and show data from 2 columns

Hi,I have an Excel file called Products.xls .I have Columns A and B, with the titles NAME and QUANTITY.The name of the sheet is SHEET1.The file has about 40 lines.How do i show these data on a Gridview or Listview ?Thank you.

Retrieve data from select columns without creating Gridview etc??

I have an accessdatasource with multiple columns, one column I use it to populate a dropdownlist. Can I use the data from other columns to insert into a table,  insert being done in the aspx.vb page, without creating gridview etc.   Is it possible to make a detailsview one column visible= false and still be able to evaluate its contents? Thanks,

Hiding rows/columns completely in SSRS tablix even when they have data

I have several columns and rows in my tablix.  I don't want to see all of them at once.  I am using a parameter that controls the visibility of the columns or rows of the tablix.  The issue is that it hides but the white space is left.  How do i take out that white space?

OLEDB/ADO Destionation - Add input column twice to destination columns or static data


(Using BIDS 2008 R2)

OLEDB/ADO Destionation

Reuse input column
Is there any way to map a input column to two destination columns?

State static value instead of mapping column
I have a package for each version of the production system - they insert in the same destination tables - I just need to add a version number to the version column - like '2.1' and '3.0'. The only way I have found so far is to select a fake column in the source like select '2.2' as Version, ..- This is however not very smart when I'm selecting millions of rows.

I would just like to be able to type in a static value instead of mapping a column - any way to do this?

How can I avoid entering Output columns manually (with their data types) in the Script Transformatio

I created a wrapper to a WCF web service that is returning many datasets with hundreds of columns.  The destination database schema matches the source schema exactly.  When i make the connection on a ADO.NET destination object to my database it auto-generates my list of columns.  I understand that the script component could output anything so it can't autogenerate at this point, but is there any way to copy and paste the Column definitions from somwhere else and paste them in?  I'd like to avoid spending hours entering all these manually.

Display columns in matrix based on a group even if data don't exist in resultset


I am trying to create columns in a matrix for data that currently does not exist.  I created a matrix that groups on office name (ROWS) and Month of Appointment Date (Columns).

For example, the matrix currently looks like this.....

  January February March April

Hide Columns in Dynamic Data - vb


I'm attempting to implement Steve's sample for this (http://csharpbits.notaclue.net/2010/02/new-way-to-do-column-generation-in.html) in vb and I've run into a bit of an issue. 

The line in Global.asax.vb:

Private Shared s_defaultModel As MetaModel = New CustomMetaModel(GetVisibleColumns)

This line is showing an error because it is looking for the columns parameter.  Do I need to pass in the columns? 

Here's the error:

Error 1 Argument not specified for parameter 'columns' of 'Public Shared Function GetVisibleColumns(columns As System.Collections.Generic.IEnumerable(Of System.Web.DynamicData.MetaColumn)) As System.Collections.Generic.IEnumerable(Of System.Web.DynamicData.MetaColumn)'. 



Group by on external data columns



I created an external content type "Auction" from a SQL table. I added an external data column "Auction" to my document library and selected a few fields to be shown as additional information on "Auction". I would like to create a view where the documents are grouped by one of those additional fields, but the field I need is not shown in the view settings... Is it possible to sort/group on the additional columns comming from from my external data column?





What new in XML Data Type if we can store data into separate columns?


Being new to XML Data Type I want to know what is the need to store our data in XML format if we can save it into separate Columns?

1. Is this practice being widely using in creating databases and maintaining information more efficiently?

2. Does this process follows Normalization rules?

3. What are the pros & cons?


Sorting or Grouping by "External Data" columns secondary fields - only possible for certain value ty


I have a BCS connection to an external system that provides various external data types.

I'm now adding an "External Data" column to a SharePoint list, selecting multiple fields of an external data type to be added as list columns. These fields have different data types, in my case string, DataTime and Boolean.

Editing the list view settings, I can only select the external entity as a whole, or it's Boolean fields for either "Sort By" or "Group By". The other fields are not offered for selection.

Is there any restrictions on which secondary fields of an external data type are supported for either sorting or grouping? Or is there something wrong in the definition of some of the external data type fields?

Thanks in advance for any help on this issue...

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend