Tutorial by Examples: column

One might want to GROUP BY more than one column declare @temp table(age int, name varchar(15)) insert into @temp select 18, 'matt' union all select 21, 'matt' union all select 21, 'matt' union all select 18, 'luke' union all select 18, 'luke' union all select 21, 'luke' union all select 1...

-- Identity primary key - unique arbitrary increment number create table person ( id int identity(1,1) primary key not null, firstName varchar(100) not null, lastName varchar(100) not null, dob DateTime not null, ssn varchar(9) not null )

Group by is often used with join statement. Let's assume we have two tables. The first one is the table of students: IdFull NameAge1Matt Jones202Frank Blue213Anthony Angel18 Second table is the table of subject each student can take: Subject_IdSubject1Maths2P.E.3Physics And because one student c...

SELECT COALESCE(NULL, NULL, 'TechOnTheNet.com', NULL, 'CheckYourMath.com'); Result: 'TechOnTheNet.com' SELECT COALESCE(NULL, 'TechOnTheNet.com', 'CheckYourMath.com'); Result: 'TechOnTheNet.com' SELECT COALESCE(NULL, NULL, 1, 2, 3, NULL, 4); Result: 1

USE AdventureWorks2012; GO CREATE FULLTEXT CATALOG production_catalog; GO CREATE FULLTEXT INDEX ON Production.ProductReview ( ReviewerName Language 1033, EmailAddress Language 1033, Comments Language 1033 ) KEY INDEX PK_ProductR...

This example shows how to optimize the column width so that column headings and data are not chopped off. alv->get_columns( )->set_optimize( ).

This example hides the MANDT (client) field from the ALV. Note that the parameter passed to get_column( ) must be capitalized in order for this to work. alv->get_columns( )->get_column( 'MANDT' )->set_visible( if_salv_c_bool_sap=>false ).

The column text may change upon the horizontal resizing of a column. There are three methods to accomplish this: Method NameMaximum Length of Headingset_short_text10set_medium_text20set_long_text40 The following example shows usage of all three. A column object is declared and instantiated as a re...

FOR JSON PATH enables you to control format of the output JSON using column aliases: SELECT top 3 object_id as id, name as [data.name], type as [data.type] FROM sys.objects FOR JSON PATH Column alias will be used as a key name. Dot-separated column aliases (data.name and data.type) will be gen...

The TIMESTAMP column will show when the row was last updated. CREATE TABLE `TestLastUpdate` ( `ID` INT NULL, `Name` VARCHAR(50) NULL, `Address` VARCHAR(50) NULL, `LastUpdate` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) COMMENT='Last Update' ;

# example data DT = as.data.table(mtcars, keep.rownames = TRUE) To rearrange the order of columns, use setcolorder. For example, to reverse them setcolorder(DT, rev(names(DT))) This costs almost nothing in terms of performance, since it is just permuting the list of column pointers in the da...

# example data DT = as.data.table(mtcars, keep.rownames = TRUE) To rename a column (while keeping its data the same), there is no need to copy the data to a column with a new name and delete the old one. Instead, we can use setnames(DT, "mpg_sq", "mpq_squared") to modify ...

# example data DT = data.table(iris) To modify factor levels by reference, use setattr: setattr(DT$Species, "levels", c("set", "ver", "vir") # or DT[, setattr(Species, "levels", c("set", "ver", "vir"))] The sec...

JSON_MODIFY function can be used to update value on some path. You can use this function to modify original value of JSON cell in UPDATE statement: update Product set Data = JSON_MODIFY(Data, '$.Price', 24.99) where ProductID = 17; JSON_MODIFY function will update or create Price key (if it do...

JSON is textual format, so it is stored in standard NVARCHAR columns. NoSQL collection is equivalent to two column key value table: CREATE TABLE ProductCollection ( Id int identity primary key, Data nvarchar(max) ) Use nvarchar(max) as you are not sure what would be the size of your JSON ...

You can expose values from JSON column as computed columns: CREATE TABLE ProductCollection ( Id int identity primary key, Data nvarchar(max), Price AS JSON_VALUE(Data, '$.Price'), Color JSON_VALUE(Data, '$.Color') PERSISTED ) If you add PERSISTED computed column, value from JSON tex...

Column Editing enables the user to edit text on several lines as a vertical square zone. This feature is enabled by default. There are 3 ways to select a zone to edit: Maintain Alt+Shift and use Up/Down/Left/Right to select the zone to edit with direction keys Maintain Alt+Shift and click on t...

pie view - (void)drawRect:(CGRect)rect { NSArray *data = @[@30, @15, @5, @17, @3, @10, @20]; // 1. context CGContextRef cxtRef = UIGraphicsGetCurrentContext(); CGPoint center = CGPointMake(150, 150); CGFloat radius = 150; __block CGFloat startAngle = 0; ...

The using of net.sf.jasperreports.export.xls.auto.filter property allow to add autofilter in generated xls file. <columnHeader> <band height="30" splitType="Stretch"> <staticText> <reportElement x="0" y="0" widt...

The change the definition of a db column, the query below can be used for example, if we have this db schema users ( firstname varchar(20), lastname varchar(20), age char(2) ) To change the type of age column from char to int, we use the query below: ALTER TABLE users CHANGE age...