Pivot table in ms sql

-- Sample data
declare @T table
(
  ID int,
  Title nvarchar(10),
  FieldsXml nvarchar(max)
)
insert into @T values
(1,     'A',       '<Fields><Field Name="X">x1</Field><Field Name="Y">y1</Field></Fields>'),
(2,     'B',       '<Fields><Field Name="Y">y2</Field><Field Name="Z">z2</Field></Fields>'),
(3,     'C',       '<Fields><Field Name="Z">z3</Field></Fields>')

-- Create temp table
select T.ID,
       T.Title,
       TN.X.value('@Name', 'nvarchar(128)') as FieldName,
       TN.X.value('.', 'nvarchar(max)') as FieldValue
into #tmp      
from @T as T    
  cross apply (select cast(FieldsXml as XML)) as TX(X)
  cross apply TX.X.nodes('/Fields/Field') as TN(X)


declare @ColList nvarchar(max)
declare @Sql nvarchar(max)

-- Build column list
select @ColList = stuff((select '], ['+FieldName
                         from #tmp
                         group by FieldName
                         for xml path('')), 1, 2, '')+']'
select * from  #tmp
-- Build query
set @Sql = 'select *
            from (select ID,
                         Title,
                         FieldName,
                         FieldValue
                  from #tmp
                 ) as T
            pivot (min(FieldValue) for FieldName in (' + @ColList + ')) as P'

exec (@Sql)

drop table #tmp

binary tree structure with child count

DECLARE @Data TABLE (
  ID INTEGER PRIMARY KEY
  , ParentID INTEGER
  , Text VARCHAR(32)
  , Price INTEGER
)

INSERT INTO @Data
  SELECT 1, Null, 'Root', NULL
  UNION ALL SELECT 2, 1, 'Flowers', NULL
  UNION ALL SELECT 3, 1, 'Electro', NULL
  UNION ALL SELECT 4, 2, 'Rose', 10
  UNION ALL SELECT 5, 2, 'Violet', 5
  UNION ALL SELECT 6, 4, 'Red Rose', 12
  UNION ALL SELECT 7, 3, 'Television', 100
  UNION ALL SELECT 8, 3, 'Radio', 70
  UNION ALL SELECT 9, 8, 'Webradio', 90

  ;WITH ChildrenCTE AS (
  SELECT  RootID = ID, ID
  FROM    @Data
  UNION ALL
  SELECT  cte.RootID, d.ID
  FROM    ChildrenCTE cte
          INNER JOIN @Data d ON d.ParentID = cte.ID
)
SELECT  d.ID, d.ParentID, d.Text, d.Price, cnt.Children
FROM    @Data d
        INNER JOIN (
          SELECT  ID = RootID, Children = COUNT(*) - 1
          FROM    ChildrenCTE
          GROUP BY RootID
        ) cnt ON cnt.ID = d.ID

demo test

demo test

Dynamic Date Picker On grid view...

$(".datagrid-row").each(function(index, element){
var id = $(this).attr("id");
  var dates = $("#"+id+" .StartDatetext, #"+id+" .EndDatetext").datepicker({
              minDate: '0',
              maxDate: '10000D',
              dateFormat: "MM dd yy",
              onSelect: function (selectedDate) {
                  var option = this.id == ".EndDatetext" ? "maxDate" : "minDate",
                    instance = $(this).data("datepicker"),
                    date = $.datepicker.parseDate(
                        instance.settings.dateFormat ||
                        $.datepicker._defaults.dateFormat,
                       selectedDate, instance.settings);

                  var odate = date;
                  if (this.id == "StartDatetext") {
                      odate.setDate(odate.getDate() + 1);
                  }
                  dates.not('.StartDatetext').datepicker("option", option, odate);
              }
          });
       });

Dynamic Date picker In Jqurey

 $(function () {
          var dates = $(".StartDtaeText, .EndDateText").datepicker({
              minDate: '0',
              maxDate: '100D',
              onSelect: function (selectedDate) {
                  var option = this.id == ".EndDateText" ? "maxDate" : "minDate",
                    instance = $(this).data("datepicker"),
                    date = $.datepicker.parseDate(
                        instance.settings.dateFormat ||
                        $.datepicker._defaults.dateFormat,
                        selectedDate, instance.settings);
                  //alert(this);
                  dates.not('.StartDtaeText').datepicker("option", option, date);
              }
          });

Can I use CASE statement in a JOIN condition?

A CASE expression returns a value from the THEN portion of the clause.

You could use it thusly: 

SELECT  * 
FROM    sys.indexes i 
    JOIN sys.partitions p 
        ON i.index_id = p.index_id  
    JOIN sys.allocation_units a 
        ON CASE 
           WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
           WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
           ELSE 0
           END = 1

Stop page post back action

 if (!IsPostBack)
            {
                ViewState["postids"] = System.Guid.NewGuid().ToString();
                Session["postid"] = ViewState["postids"].ToString();
            }
            else
            {
                if (ViewState["postids"].ToString() != Session["postid"].ToString())
                {
                    IsPageRefresh = true;
                }
                Session["postid"] = System.Guid.NewGuid().ToString();
                ViewState["postids"] = Session["postid"].ToString();
            }