Converting Columns into rows with their respective data in sql server (UNPIVOT)

Sound like you want to UNPIVOT

Sample from books online:

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM 
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN 
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

Returns:

VendorID   Employee   Orders
---------- ---------- ------
1          Emp1       4
1          Emp2       3
1          Emp3       5
1          Emp4       4
1          Emp5       4
2          Emp1       4
2          Emp2       1
2          Emp3       5
2          Emp4       5
2          Emp5       5

How many non clustered index in a table?

SQL Server 2005: 1 Clustered Index + 249 Nonclustered Indexes = 250 Indexes per table

SQL Server 2008: 1 Clustered Index + 999 Nonclustered Indexes = 1000 Indexes per table

Syntax to create Non Clustered Index----

CREATE NONCLUSTERED INDEX IX_tblBooks_Auhthor_id

on tblBooks (Auhthor_id)

In above syntax tblBooks is Table Name and Auhthor_id is Column name on which index is created.

The datatype of index_id in sysindexes means:

·         0 for heap

·         1 - clustered index

·         >1 nonclustered

·         >=3200 - XML indexes

So, we can still observe increase up to 3198(3199-1) in future versions of SQL Server.

I thought previously that sys.indexes is synonym to sysindexes but I found just now that they are different, sysindexes has indid (instead of index_id) and does not contain rows for XML indexes!

index_id from sys.indexes has type int(4bytes) and indid from sys.sysindexes has type smallint (2bytes) (SQL Server 2008, probably increased from previous versions)

Filtering Distinct using DataView

 Actual Table

DataTable dt = new DataTable();

DataView dv = new DataView(dt);

gv.DataSource = dv.ToTable(true,new string[]  "ProductId","ProductName","IsActive"});

gv.DataBind();


After Apply Rowfilter----

Read Data from XML Parameter and Perform Insert /Update in SQL server

create table #temp

(

  ProductId int,

  PriceType int,

  Price decimal(18,2)

)

------------------Read XML Node's Attribute Value-------------------------

Declare @hdoc int

DECLARE @XmlData  varchar(max)='',@ProductId int=1,@XML xml

SET @XmlData='<Data><Item PriceType="1" Price="100" /><Item PriceType="2" Price="120" /><Item PriceType="3" Price="80" /></Data>'

SET @XML=CAST(@XmlData AS XML)

EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML

--insert into #temp (ProductId,PriceType,Price)

SELECT @ProductId,x.PriceType,x.Price FROM OPENXML(@hdoc,'Data/Item')WITH

(

  PriceType Int '@PriceType',

  Price decimal(18,2) '@Price'

)x

EXEC sp_xml_removedocument @hdoc

select * from #temp

------------------Read XML Node Value----------------------------------

Declare @hdoc1 int

DECLARE @XmlData1  varchar(max)='',@ProductId1 int=1,@XML1 xml

SET @XmlData1='<Data><Items><Product>Hotel</Product><Price>100</Price></Items></Data>'

SET @XML1=CAST(@XmlData1 AS XML)

EXEC sp_xml_preparedocument @hdoc1 OUTPUT, @XML1

--insert into #temp (ProductId,PriceType,Price)

SELECT @ProductId1 'ProductId',x.Product,x.Price FROM OPENXML(@hdoc1,'Data/Items')WITH

(

  Product varchar(10) 'Product',

  Price decimal(18,2) 'Price'

)x

EXEC sp_xml_removedocument @hdoc1

Angular 2 Lifecycle

Angular 2 Lifecycle sequence

After creating a component/directive by calling its constructor, Angular calls the lifecycle hook methods in the following sequence at specific moments:

• 1.  ngOnChanges()
• 2.  ngOnInit()
• 3.  ngDoCheck()
• 4.  ngAfterContentInit()
• 5.  ngAfterContentChecked()
• 6.  ngAfterViewInit()
• 7.  ngAfterViewChecked()
• 8.  ngOnDestroy()

Construsctor: Constructor of commponent are called before any other component life cyclie hoooks If our component is based on any dependences then constructor is the best place to inject  those dependecies.

import {Component} from 'angular2/core';

import {CarService} from './carService';

@Component({

  selector: ‘list-cars’,

  template: `

    <ul>

      <li *ngFor="#car of cars">{{car.name}}</li>

    </ul>

  `

})

class AppComponent {

  cars:Array<any>;

  constructor(private _carService: CarService) {

   this.cars = _carService.getCars();

  }

}

ngOnInit:

The ngOnInit method of a component is called directly after the constructor and after the ngOnChange is triggered for the first time. It is the perfect place for initialisation work.

ngOnChanges:

The ngOnChanges will be called first when the value of a bound property changes. It executes, every time the value of an input property changes. It will receive a changes map, containing the current and previous values of the binding, wrapped in a SimpleChange.

{"brand":{"previousValue":"","currentValue":"BMW"}}

In the case above, one change to the input property brand is reported. The value of this property has been changed from an empty string to the string “BMW”.

ngOnDestroy:

The ngDestroy is called in a component’s lifecycle just before the instance of the component is finally destroyed. It is the perfect place to clean the component — for example, to cancel background tasks.

Quick example:

@Directive({

  selector: '[destroyDirective]'

})

export class OnDestroyDirective implements OnDestroy {

sayHello: number;

constructor() {

  this.sayHiya = window.setInterval(() => console.log('hello'),     1000);

}

ngOnDestroy() {

   window.clearInterval(this.sayHiya);

}

}

If we do not use the ngOnDestroy method we will have the thread logging “hello” until the end or it crashes….

More advanced phases are:

ngDoCheck:

ngDoCheck is triggered every time the input properties of a component or a directive are checked. We can use this lifecycle hook to extend the check with our own custom check logic. It can also be useful if we want to accelerate the change detection by checking the bare minimum and not using the default algorithm (although we usually do not use this).

ngAfterContentInit:

The ngAfterContentInit lifecycle hook is called after ngOnInit when the component or directive’s content has been initialised; basically when all the bindings of the component have been checked for the first time.

ngAfterContentChecked:

Called after every check of the component or directive’s content, effectively when all the bindings of the components have been checked; even if they haven’t changed.

ngAfterViewInit:

Called after ngAfterContentInit when the component’s view has been initialised. Applies to components only.

ngAfterViewChecked:

Called after every check of the component’s view. Applies to components only. When all the bindings of the children directives have been checked; even if they haven’t changed. It can be useful if the component is waiting for something coming from its child components.