Today I will show how to write a query in order to convert the columns to rows in SQL Server 2008 without using the unpivot function:
The INSERT INTO statement is used to insert new records in a table. But in SQL Server 2005 we cannot write an insert into statement & insert the whole tables or output values into a table. We have some other approaches out of which I'll showcase 3 of them in my post as mentioned below:
With the Editor you can format your code with indenting, hidden
text, URLs, and so forth. You can also auto-format your code as you type by
using Smart Indenting.
You can choose three different styles of text indenting. You can also specify how many spaces compose a single indentation or tab, and whether the Editor uses tabs or space characters when indenting.
1.
On the Tools menu,
click Options.
2.
Click Text
Editor.
3.
Click the folder, and select All Languages to
set indenting for all languages.
4.
Click Tabs.
5.
Click one of the following options:
o None. The
cursor goes to the beginning of the next line.
o Block. The
cursor aligns the next line with the previous line.
o Smart (Default).
The language service determines the appropriate indenting style to use.
1.
On the Tools menu,
click Options.
2.
Click Text
Editor.
3.
Select the folder for All Languages to set indenting for all
languages.
4.
Click Tabs.
5.
To specify tab characters for tab and indent operations,
click Keep tabs.
To specify space characters, select Insert
spaces.
If you select Insert Spaces, enter the number of space characters each tab or indent represents under Tab Size or Indent Size, respectively.
1.
Select the text you want to indent.
2.
Press TAB, or click the Indent button on the Standard toolbar.
1.
Select the text you want to unindent.
2. Press SHIFT+TAB, or click the Unindent button on the Standard toolbar.
To automatically indent all of your code
1.
On the Tools menu,
click Options.
2.
Click Text
Editor.
3.
Click All
Languages.
4.
Click Tabs.
5.
Click Smart.
1.
Select the text whose white space you want to convert to tabs.
2.
On the Edit menu,
point to Advanced,
and click Tabify
Selection.
2.
On the Edit menu,
point to Advanced,
and click Untabify
Selection.
The behavior of these commands depends on the tab settings in
the Options dialog
box. For example, if the tab setting is 4, Tabify Selection creates a tab for
every 4 contiguous spaces, and Untabify
Selection creates 4 spaces for every tab.
1.
Select the text you want to convert.
2.
To convert text to uppercase, press CTRL+SHIFT+U, or click Make Uppercase on
the Advanced submenu
of the Edit menu.
3.
To convert text to lowercase, press CTRL+SHIFT+L, or click Make Lowercase on
the Advanced submenu
of the Edit menu.
You can create and display clickable URLs in your code. By
default, the URLs:
·
Are underlined.
·
Change the mouse pointer to a hand when you move over them.
·
Open the URL when clicked, if the URL is valid.
1.
On the Tools menu,
click Options.
2.
Click Text
Editor.
3.
To change the option for only one language, click that language
folder and then click General.
To change the option for all languages, click All Languages and
then click General.
4.
Select Enable
single-click URL navigation.
Step 1: Create a procedure in order to replace the last occurrence of a string with a specified string as shown below:
Step 2: Call the SP created above and pass the string in which we need to search the last occurrence of a string & replace with a specified string as shown below:
Step 3: The Output will be as shown below:
Step 2: Create an SP that will break the CSV string into columns and insert the data into the above created table as shown below:
Create a function that will accept the "text string" and "character" where we want to split the string:
ALTER FUNCTION [dbo].[funSplit]
(
@Texto varchar(max),
@Splitter varchar(3)
)
RETURNS
@Lista TABLE
(
rowData varchar(8000)
)
AS
BEGIN
DECLARE @Pos Smallint
While len(@Texto)>0 BEGIN
SET @Pos = Patindex('%'+@Splitter+'%',@Texto)
IF @Pos > 0 BEGIN
INSERT INTO @Lista
SELECT left(@Texto, @Pos-1)
SET @Texto = right(@Texto, len(@Texto)-@Pos)
END
ELSE BEGIN
INSERT INTO @Lista
SELECT @Texto
SET @Texto = ''
END
End
RETURN
END
GO
Now call the function pass the "sample string" & "character" to split as shown below:
The output of the query would look like below, that is our desired output:
Create a stored procedure that would take the input as XML data & then break down the data to columns depending upon the XML tags formed:
Now call the stored procedure we created & pass the XML string as input to the SP:
<Info>
<Data>
<EmpId>100</EmpId>
<EmpName>ABC</EmpName>
<Address>My Address</Address>
</Data>
<Data>
<EmpId>101</EmpId>
<EmpName>ABC1</EmpName>
<Address>My Address1</Address>
</Data>
<Data>
<EmpId>102</EmpId>
<EmpName>ABC2</EmpName>
<Address>My Address2</Address>
</Data>
</Info>
The output of the query would look like below, that is our desired output:
--Declared a table variable that will act as a temp table to hold the values
DECLARE @tbl_char TABLE (input VARCHAR(100),letter VARCHAR(1),remainder VARCHAR(100))--SET the input & the character you want to search
DECLARE @Input VARCHAR(100)='suryapratapsingh', @characterToFind VARCHAR(1)='a', @remainder VARCHAR(100), @letter VARCHAR(100)=''--WHILE loop to iterate through all the remainder values of the string
--Getting the count of the letter required by using a group by clause
Below is the output
that we receive when we pass the inputstring=’suryapratapsingh’ and character_to_search=’a’:
Check for the publication properties subscription expiration setup. If it is set to "never expire" as you can see from the screen capture below.
First point that you need to keep in mind before you insert any null value in a SQL Server Table column is that you need to make the column to accept nulls by default which can be mentioned while creation of the Table by using the create table command as follows:
Example:
CREATE TABLE dbo.Students(Id INT IDENTITY(1,1) PRIMARY KEY, age INT default null, gender CHAR(1))
GO
Or if the table is created then Go-to the Database->TableName->Design->Column->Properties
and set the default value as null-able.
Now if you send only those values that are to be
inserted & has some value then those columns which has values will be inserted
& the columns for which no values have been passed so by default NULL
value will be inserted for those columns which are made null-able by default.
Hence, there is no need to pass null value from a front-end application to SQL Server it will automatically handle it at its end only pass the columns that have some value.
DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.
Syntax of table creation:
CREATE TABLE [SCHEMA].TABLE_NAME(COLUMN_NAME COLUMN_DATATYPE)Example of table creation:
CREATE TABLE dbo.Students(Id INT IDENTITY(1,1) PRIMARY KEY, age INT, gender CHAR(1))Syntax of view creation:
CREATE VIEW [SCHEMA].VIEW_NAMEExample of view creation:
CREATE VIEW dbo.Students_VWSyntax of dropping a table:
DROP TABLE [SCHEMA].TABLE_NAMEExample of dropping a table:
DROP TABLE dbo.StudentsSyntax of altering size of a column in table:
ALTER TABLE [SCHEMA].TABLE_NAMEExample of altering a column size of a table:
ALTER TABLE dbo.StudentsSyntax of adding a new column in a table:
ALTER TABLE [SCHEMA].TABLE_NAMEExample of adding a new column in a table:
ALTER TABLE dbo.StudentsSyntax of truncating a table:
TRUNCATE TABLE [SCHEMA].TABLE_NAMEExample of truncating a table:
TRUNCATE TABLE dbo.StudentsSo today we saw the SQL DDL Commands that we use very frequently while writing queries in SQL Server.
