How To Convert rows into Column in SQL?

Question

Actually I have query which returns me 5 columns and values(date) for each or them in a single row but what I want is the columns names should be in a single column Title and its (Vale) in second columns in Date.

Note Title and Date are the columns of the temporary table I just created run time.

Here is my piece of code I am using,

protected void btnAlerts_Click(object sender, EventArgs e)
{
       fnSaveAlerts();
}

private void fnSaveAlerts()
{
    DataTable dtTemp = new DataTable();

    DataColumn dtC1 = new DataColumn();
    dtC1.DataType = typeof(String);
    dtC1.ColumnName = "Title";

    DataColumn dtc2 = new DataColumn();
    dtc2.DataType = typeof(DateTime);
    dtC1.ColumnName = "Date";

    dtTemp.Columns.Add(dtC1);
    dtTemp.Columns.Add(dtc2);

    LeadsContracts ObjLeadsContract = new LeadsContracts();

    ObjLeadsContract.ExecuteSql(@"select dtDisclosure, dtDueDiligence, 
                                         dtFinanceAppraisals, dtFreeTextDate1, 
                                         dtFreeTextDate2 
                                  from LeadsContracts 
                                  where dtDisclosure is not null 
                                    and dtDueDiligence is not null 
                                    and dtFinanceAppraisals is not null 
                                    and dtFreeTextDate1 is not null 
                                    and dtFreeTextDate2 is not null");

    dtTemp.DataSet = ObjLeadsContract.DefaultView;  // it also gives error here "Red line"

}

I have already spend whole day to do this, please any body can help me. thanks in advance

score 0 · Answer 1 · answered May 22 '13 at 12:09

I'll give you hints for a solution:
For your problem you can use a two-dimenstional array.

[0]["Title", DatabaseValue]
[1]["Date", DatabaseValue]
[2][
[3][
[4][

So, first loop over the five values of your query and fill into the two dimensional array

[0][1]
[1][1]
[2][1]
[3][1]
[4][1]

Then you can loop over the two dimensional array for your output...

watraplion · Answer 2 · 2013-05-23T04:44:30.103

Are you expecting something like this?

DECLARE @Temp AS TABLE (dtDisclosure varchar(20),dtDueDiligence varchar(20),dtFinanceAppraisals varchar(20),dtFreeTextDate1 varchar(20),dtFreeTextDate2 varchar(20))
INSERT INTO @Temp VALUES ('20-Apr-2013','20-Apr-2013','20-Apr-2013','20-Apr-2013','20-Apr-2013'),
                        ('21-Apr-2013','21-Apr-2013','21-Apr-2013','21-Apr-2013','21-Apr-2013')

SELECT [Title],[Date] FROM
(
   SELECT * FROM @temp
} A
UNPIVOT
{
    [Date] FOR [Title] IN     IN ([dtDisclosure],[dtDueDiligence],[dtFinanceAppraisals],[dtFreeTextDate1],[dtFreeTextDate2])
} AS Unpvt;

OUTPUT :

Title                 Date
dtDisclosure          20-Apr-2013
dtDueDiligence        20-Apr-2013
dtFinanceAppraisals   20-Apr-2013
dtFreeTextDate1       20-Apr-2013
dtFreeTextDate2       20-Apr-2013
dtDisclosure          21-Apr-2013
dtDueDiligence        21-Apr-2013
dtFinanceAppraisals   21-Apr-2013
dtFreeTextDate1       21-Apr-2013
dtFreeTextDate2       21-Apr-2013

this is fine but what if we don't know the dates I mean how it will work if v have more than one row or records? — Saeed Khan, May 22 '13 at 13:36
Check out this ...if this is what you want....rows to columns conversion.........http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server-2008 — Qasim Javaid Khan, May 23 '13 at 05:23

How To Convert rows into Column in SQL?

2 Answers2