converting Rows to columns in SQL Server.

http://weblogs.asp.net/salimfayad/archive/2008/01/30/rows-to-columns.aspx


------------------------::--------------
Rows to Columns How to convert rows into columns? We're going to see how to do that using query, and there is the "Pivot" function in SQL Server 2005.

The easiest way is when you know the columns which are fixed. But most of the times, you want to do that in general not for specific columns.

Using Query:

If you have fixed columns, then all you will be doing is an “if-else” field that holds the column names and set them into different columns:

Example: We have 2 tables: LKUP_Subject which is a lookup table that holds the subject names, and the Student_Subject which contains the student grades for the different subject.



We are going to build the query having fixed columns:

We use the "if-else" functionality in the query to put them in the defined columns (in SQL, it is "case"):
SELECT StudentId,
(CASE SubjectId WHEN 24 THEN ISNULL(Grade, 0) END) AS Physics,
(CASE SubjectId WHEN 25 THEN ISNULL(Grade, 0) END) AS Chemistry,
(CASE SubjectId WHEN 26 THEN ISNULL(Grade, 0) END) As Math,
(CASE SubjectId WHEN 28 THEN ISNULL(Grade, 0) END) AS English
FROM Student_Subject






2. Then we use the “SUM” function to merge the results in 1 row like we want:

SELECT StudentId,
SUM(Physics) AS Physics,
SUM(Chemistry) As Chemistry,
SUM(Math) AS Math,
SUM(English) As English
FROM
(SELECT StudentId,
(CASE SubjectId WHEN 24 THEN ISNULL(Grade, 0) END) AS Physics,
(CASE SubjectId WHEN 25 THEN ISNULL(Grade, 0) END) AS Chemistry,
(CASE SubjectId WHEN 26 THEN ISNULL(Grade, 0) END) As Math,
(CASE SubjectId WHEN 28 THEN ISNULL(Grade, 0) END) AS English
FROM Student_Subject) s
GROUP BY StudentId







Now, we will build it dynamically using cursor (you can do it using temporary tables to do the same functionality for performance reasons):



DECLARE Cur CURSOR FOR
SELECT DISTINCT id, '[' + Description_En + ']' AS Description_En
FROM LKUP_Subject

DECLARE @SubjectName NVARCHAR(MAX),
@SubjectId INT,
@Sum NVARCHAR(MAX), -- The SUM part of the query
@Select NVARCHAR(MAX), -- The inner query
@Sql NVARCHAR(MAX) -- The total sql statement

SET @Select = ''
SET @Sum = ''
SET @Sql = ''

OPEN Cur


FETCH NEXT FROM Cur INTO @SubjectId, @SubjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sum = @Sum + 'SUM(' + @SubjectName + ') AS ' + @SubjectName + ','
SET @Select = @Select + '(CASE WHEN SubjectId = ' + CONVERT(NVARCHAR(10), @SubjectId) + ' THEN Grade END) AS ' + @SubjectName + ','
FETCH NEXT FROM Cur INTO @SubjectId, @SubjectName

END

CLOSE Cur
DEALLOCATE Cur


IF RIGHT(@Select, 1) = ','
SET @Select = STUFF(@Select, LEN(@Select), 1, ' FROM Student_Subject')

IF RIGHT(@Sum, 1) = ','
SET @Sum = STUFF(@Sum, LEN(@Sum), 1, '')

SET @Sql = 'SELECT StudentId, ' + @Sum + ' FROM (SELECT StudentId, ' + @Select + ') s GROUP BY StudentId'

EXEC sp_executesql @Sql



Using Pivot:

In SQL Server 2005, there is a new feature that does all of this in a single step: PIVOT

In fixed columns, here is how we use it:

SELECT StudentId, Physics, Chemistry, Math, English
FROM
(SELECT StudentId, Grade, Description_En
FROM LKUP_Subject
INNER JOIN Student_Subject
ON LKUP_Subject.Id = Student_Subject.SubjectId) S
PIVOT
(
SUM (Grade)
FOR Description_En IN
(Physics, Chemistry, Math, English)) AS pvt



Note: that you should use an aggreate function like the SUM (for the same reason as you should use an aggreate function when using the query to transform Rows to Columns)


As for how to do it dynamically:

DECLARE Cur CURSOR FOR
SELECT DISTINCT Description_En
FROM LKUP_Subject

DECLARE @Temp NVARCHAR(MAX),
@AllSubjects NVARCHAR(MAX),
@SubjectQuery NVARCHAR(MAX)

SET @AllSubjects = ''

OPEN Cur
-- Getting all the subjects
FETCH NEXT FROM Cur INTO @Temp
WHILE @@FETCH_STATUS = 0
BEGIN
SET @AllSubjects = @AllSubjects + '[' + @Temp + '],'
FETCH NEXT FROM Cur INTO @Temp
END

CLOSE Cur
DEALLOCATE Cur

SET @AllSubjects = SUBSTRING(@AllSubjects, 0, LEN(@AllSubjects))

-- Building the pivot query
SET @SubjectQuery = 'SELECT StudentId, ' + @AllSubjects + '
FROM
(SELECT StudentId, Grade, Description_En
FROM LKUP_Subject
INNER JOIN Student_Subject
ON LKUP_Subject.Id = Student_Subject.SubjectId) S
PIVOT
(
SUM (Grade)
FOR Description_En IN
(' + @AllSubjects + ')) AS pvt'

EXEC sp_executesql @SubjectQuery

javascript:: for getting window height;

1.function winHt()
2.{
3.var ht='';
4.ht=document.body.clientHeight;//gets the body height of window opened.
5.alert(ht);
6.}

Convert Number to words (for Indian rupees and other currency ) in C# Asp.Net

public class NumberToWordsForCurrency
{


#region "--Methods--"
public string ConvertToInteranationWords(string numb)
{
string RetVal = string.Empty;
numb = numb.Trim();
if (this.IsNumber(numb))
{
try
{
string currency = "$ ";
string _currency = " cents Only.";
string Number;
string deciml;
string _number;
string _deciml;

this.Initialize();
string[] no = numb.Split('.');
if (no.Length == 1)
{
if (no[0].Length >= 1 && Convert.ToDouble(no[0]) > 0)
{
Number = no[0];
_number = (NameOfNumber(Number));
RetVal = currency + _number + " Only.";
}
else
{
RetVal = currency + " Zero Only.";
}

}
else if (no.Length == 2)
{
//int number part
if (no[0].Length >= 1 && Convert.ToDouble(no[0]) > 0)
{
Number = no[0];
_number = (NameOfNumber(Number));
}
else
{
_number = "Zero";
}
//decimal number part
if (no[1].Length >= 1 && Convert.ToDouble(no[1]) > 0)
{
deciml = no[1];
if (no[1].Length == 1)
{
deciml = Convert.ToString(Convert.ToDouble(no[1]) * 10);
}
_deciml = (NameOfNumber(deciml));
}
else
{
_deciml = "Zero";
}
// when both int and decimal part conbined.
RetVal = currency + _number.Trim() + " and " + _deciml.Trim() + _currency;
}

}
catch (Exception ex)
{

}
}
else
{
RetVal = "Invalid Value";
}

return RetVal;
}
#region "--Private variables--"
string[] US = new string[1003];
string[] SNu = new string[20];
string[] SNt = new string[10];

#endregion

///

///
///

private void Initialize()
{
SNu[0] = "";
SNu[1] = "One";
SNu[2] = "Two";
SNu[3] = "Three";
SNu[4] = "Four";
SNu[5] = "Five";
SNu[6] = "Six";
SNu[7] = "Seven";
SNu[8] = "Eight";
SNu[9] = "Nine";
SNu[10] = "Ten";
SNu[11] = "Eleven";
SNu[12] = "Twelve";
SNu[13] = "Thirteen";
SNu[14] = "Fourteen";
SNu[15] = "Fifteen";
SNu[16] = "Sixteen";
SNu[17] = "Seventeen";
SNu[18] = "Eighteen";
SNu[19] = "Nineteen";
SNt[2] = "Twenty";
SNt[3] = "Thirty";
SNt[4] = "Forty";
SNt[5] = "Fifty";
SNt[6] = "Sixty";
SNt[7] = "Seventy";
SNt[8] = "Eighty";
SNt[9] = "Ninety";

US[1] = "";
US[2] = "Thousand";
US[3] = "Million";
US[4] = "Billion";
US[5] = "Trillion";
US[6] = "Quadrillion";
US[7] = "Quintillion";
US[8] = "Sextillion";
US[9] = "Septillion";
US[10] = "Octillion";


}
///

///
///

///
///
public string NameOfNumber(string Number)
{
string GroupName = "";
string OutPut = "";
if ((Number.Length % 3) != 0)
{
Number = Number.PadLeft((Number.Length + (3 - (Number.Length % 3))), '0');
}
string[] Array = new string[Number.Length / 3];
Int16 Element = -1;
Int32 DisplayCount = -1;
bool LimitGroupsShowAll = false;
int LimitGroups = 0;
bool GroupToWords = true;
for (Int16 Count = 0; Count <= Number.Length - 3; Count += 3)
{
Element += 1;
Array[Element] = Number.Substring(Count, 3);
}
if (LimitGroups == 0)
{
LimitGroupsShowAll = true;
}
for (Int16 Count = 0; (Count <= ((Number.Length / 3) - 1)); Count++)
{
DisplayCount++;
if (((DisplayCount < LimitGroups) || LimitGroupsShowAll))
{
if (Array[Count] == "000") continue;
{
GroupName = US[((Number.Length / 3) - 1) - Count + 1];
}
if ((GroupToWords == true))
{
OutPut += Group(Array[Count]).TrimEnd(' ') + " " + GroupName + " ";
}
else
{
OutPut += Array[Count].TrimStart('0') + " " + GroupName;
}
}
}
Array = null;
return OutPut;
}
///

///
///

///
///
private string Group(string Argument)
{
string Hyphen = ""; string OutPut = ""; Int16 d1 = Convert.ToInt16(Argument.Substring(0, 1));
Int16 d2 = Convert.ToInt16(Argument.Substring(1, 1));
Int16 d3 = Convert.ToInt16(Argument.Substring(2, 1));
if ((d1 >= 1))
{
OutPut += SNu[d1] + " hundred ";
}
if ((double.Parse(Argument.Substring(1, 2)) < 20))
{
OutPut += SNu[Convert.ToInt16(Argument.Substring(1, 2))];
}
if ((double.Parse(Argument.Substring(1, 2)) >= 20))
{
if (Convert.ToInt16(Argument.Substring(2, 1)) == 0)
{
Hyphen += " ";
}
else
{
Hyphen += " ";
}
OutPut += SNt[d2] + Hyphen + SNu[d3];
}
return OutPut;
}
///

/// Test whether string is a number or not.
///

///
/// True if its a valid number.
private Boolean IsNumber(string num)
{
Boolean RetVal = false;
num = num.Trim();
if (num.Length > 0)
{
try
{
double test;
test = Convert.ToDouble(num);
RetVal = true;
}
catch (System.FormatException ex)
{
//throw;
}
catch (Exception ex)
{
//throw;
}
}
return RetVal;
}


public string ConvertToINR(string numb)
{
string RetVal = string.Empty;
numb = numb.Trim();
if (this.IsNumber(numb))
{
try
{
string currency = "Rupees ";
string _currency = " Paise Only.";
string Number;
string deciml;
string _number;
string _deciml;

this.Initialize();
string[] no = numb.Split('.');
if (no.Length == 1)
{
if (no[0].Length >= 1 && Convert.ToDouble(no[0]) > 0)
{
Number = no[0];
_number = (NameOfINRNumber(Number));
RetVal = currency + _number + " Only.";
}
else
{
RetVal = currency + " Zero Only.";
}

}
else if (no.Length == 2)
{
//int number part
if (no[0].Length >= 1 && Convert.ToDouble(no[0]) > 0)
{
Number = no[0];
_number = (NameOfINRNumber(Number));
}
else
{
_number = "Zero";
}
//decimal number part
if (no[1].Length >= 1 && Convert.ToDouble(no[1]) > 0)
{
deciml = no[1];
if (deciml.Length > 2)
{
deciml = deciml.Substring(0, 2);
}
if (no[1].Length == 1)
{
deciml = Convert.ToString(Convert.ToDouble(no[1]) * 10);
}
_deciml = (NameOfINRNumber(deciml));
}
else
{
_deciml = "Zero";
}
// when both int and decimal part conbined.
RetVal = currency + _number.Trim() + " and " + _deciml.Trim() + _currency;
}

}
catch (Exception ex)
{

}
}
else //not a number
{
RetVal = "Invalid Input.";
}
return RetVal;

}

private string NameOfINRNumber(string Number)
{
string RetVal = string.Empty;
try
{
int len = Number.Length;
if (len >= 0 && len <= 5)
{
RetVal = (NameOfNumber(Number));
}
else if (len > 5 && len <= 7)
{
RetVal = NameOfNumber(Number.Substring(0, len - 5)) + " Lakh " + NameOfNumber(Number.Substring(len - 5));
}
else if (len > 7 && len <= 9)
{
RetVal = NameOfNumber(Number.Substring(0, len - 7)) + " Crore " + NameOfNumber(Number.Substring(len - 7, 2)) + " Lakh " + NameOfNumber(Number.Substring(len - 5));
}
else if (len > 9 && len <= 11)
{
RetVal = NameOfNumber(Number.Substring(0, len - 9)) + " Arab " + NameOfNumber(Number.Substring(len - 9, 2)) + " Crore " + NameOfNumber(Number.Substring(len - 7, 2)) + " Lakh " + NameOfNumber(Number.Substring(len - 5));
}
else if (len > 11 && len <= 13)
{
RetVal = NameOfNumber(Number.Substring(0, len - 11)) + " Kharab " + NameOfNumber(Number.Substring(len - 11, 2)) + " Arab " + NameOfNumber(Number.Substring(len - 9, 2)) + " Crore " + NameOfNumber(Number.Substring(len - 7, 2)) + " Lakh " + NameOfNumber(Number.Substring(len - 5));
}
else if (len > 13)
{
RetVal = NameOfNumber(Number);
}

}
catch (Exception ex)
{
//throw new Exception("The method or operation is not implemented.");
}
return RetVal;
}

#endregion






}