Sql-Server-Query-World

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:

Step 1: Let's analyse the input first which is as shown below:

Convert columns to rows in SQL Server 2008 without unpivot

Step 2: Now lets write a query that would help us inorder to get the columns into rows as shown below:

DECLARE @rowId INT=1,@noOfRows INT=0,@id varchar(20),@age varchar(20),@gender varchar(20)
--First get the no of rows in the table
SET @noOfRows=(SELECT count(*) FROM students)

--create a table variable to store the output data required
DECLARE @tbl TABLE
(
      col1 VARCHAR(20)
)

--Put a while loop to iterate through all the rows in the table one by one:
WHILE(@rowId<=@noOfRows)
BEGIN
      SELECT @id=CAST(id AS VARCHAR),@age=CAST(age AS VARCHAR),@gender=gender FROM students WHERE id=@rowId

      Insert into @tbl (col1) values (@id)
      Insert into @tbl (col1) values (@age)
      Insert into @tbl (col1) values (@gender)

      --increment the @rowId counter by 1 to get the next row in the next iteration
      SET @rowId=@rowId+1;
END

SELECT * FROM @tbl

Step 3: The output will be as shown below:

Insert INTO table from select query in SQL Server 2005

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:

1st Approach:

INSERT INTO [MyDB].[dbo].[MyTable]
      ([FieldID]
      ,[Description])
VALUES
      (1000,N'test')

2nd Approach:

INSERT INTO [MyDB].[dbo].[MyTable]
       ([FieldID]
       ,[Description])
VALUES
       (1001,N'test2')
One other option is to use UNION ALL:
INSERT INTO [MyDB].[dbo].[MyTable]
      ([FieldID]
       ,[Description])
SELECT 1000, N'test' UNION ALL
SELECT 1001, N'test2'

3rd Approach:

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
GO

How to convert binary to decimal in SQL Server?

Today I will show you how we can write a query in order to convert Binary to Decimal in SQL Server and vice-versa:

1. Query to convert Binary to Decimal value:

DECLARE @input varchar(max) = '1010'

;WITH N(V) AS
(
    SELECT
        ROW_NUMBER()over(ORDER BY (SELECT 1))
    FROM
        (VALUES(1),(1),(1),(1))M(a),
        (VALUES(1),(1),(1),(1))L(a),
        (VALUES(1),(1),(1),(1))K(a)
)
SELECT SUM(SUBSTRING(REVERSE(@input),V,1)*POWER(CAST(2 as BIGINT), V-1))
FROM N
WHERE V <= LEN(@input)

2. Query to convert Decimal to Binary value:

DECLARE @input BIGINT = 10

;WITH N(N)AS
(
    SELECT top 63
        POWER(cast(2 as bigint),
        ROW_NUMBER()over(ORDER BY (SELECT 1))-1)
    FROM
         (VALUES(1),(1),(1),(1))M(a),
        (VALUES(1),(1),(1),(1))L(a),
        (VALUES(1),(1),(1),(1))K(a)
)
SELECT
    COALESCE
    (
         REVERSE
         (
             (
                SELECT CAST(@input/N%2 as CHAR(1))
                FROM N
                WHERE N <= @input
                for xml path(''), type
             ).value('.', 'varchar(max)')
            )
         , '0'
)

Code Formatting in Stored Procedure on any version of SQL Server:

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.

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.

Formatting stored procedure in SQL Server 2014?

To choose an indenting style:

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.

To change indent tab settings:

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.

To indent code:

1. Select the text you want to indent.

2. Press TAB, or click the Indent button on the Standard toolbar.

To unindent code:

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.

To convert white space to tabs:

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.

To convert tabs to spaces:

1. Select the text whose tabs you want to convert to spaces.

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.

Converting Text to Upper and Lower Case

You can use commands to convert text to all uppercase or lower case.

To switch text to upper or lower case:

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.

Displaying and Linking to URLs

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.

To display a clickable URL:

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.

How to convert multiple rows into single row in SQL Server 2008?

Today we will go through the steps in order to write the simplest query to combine multiple rows into single row in SQL Server 2008:

Step 1: Create a table

CREATE TABLE [dbo].[Students]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[age] [int] NULL DEFAULT (NULL),
[gender] [char](1) NULL
)
GO

Step 2: Insert values into the table created in Step 1

INSERT INTO Students (age,gender) VALUES (24,'M')
INSERT INTO Students (age,gender) VALUES (26,'F')
INSERT INTO Students (age,gender) VALUES (25,'F')

Step 3: Below is the query to get multiple rows data in a single row and in a single column

DECLARE @singleRowData NVARCHAR(MAX)=''
SELECT @singleRowData=@singleRowData+CAST(Id AS VARCHAR)+':'+CAST(age AS VARCHAR)+':'+gender+';' FROM [dbo].[Students]

SELECT @singleRowData AS 'singleRowOutput'

Step 4: The output of the query would be as below:

Conclusion:

Today we saw how we can write a query in order to group multiple rows data into a single row & in a single column.

Step 1: Create a procedure in order to replace the last occurrence of a string with a specified string as shown below:

CREATE PROC sp_ReplaceStringWithLastOccurence
(
      @Texto varchar(MAX),
      @findString VARCHAR(100),
      @replaceString VARCHAR(100)
)
AS
BEGIN
    DECLARE @Pos INT=0,@startPos INT=0,@endPos INT=0,@totalStringLength INT=LEN(@Texto),@Splitter CHAR(1)=' ',@initialString
VARCHAR(MAX)=@Texto
  DECLARE @Lista TABLE (
      rowData VARCHAR(8000),
      startPos INT,
      endPos INT
  )
  WHILE LEN(@Texto)>0
  BEGIN
      SET @startPos=@startPos+@Pos
      SET @Pos = PATINDEX('%'+@Splitter+'%',@Texto)
      SET @endPos=@Pos-1

      IF @Pos > 0
      BEGIN
        INSERT INTO @Lista(rowData,startPos,endPos)
        SELECT left(@Texto, @Pos-1),@startPos+1,@endPos

        SET @Texto = right(@Texto, LEN(@Texto)-@Pos)
      END
      ELSE
      BEGIN
        DECLARE @end INT=0
        set @end=@totalStringLength+1-@startPos

        INSERT INTO @Lista(rowData,startPos,endPos)
        SELECT @Texto,@startPos+1,@end

        SET @Texto = ''
      END
    END

--Break the input string into by space using the split function
DECLARE @startPos1 INT=0,@endPos1 INT=0
SELECT TOP 1
@startPos1=startPos,
@endPos1=startPos+endPos
FROM
@Lista
WHERE
rowData=@findString
ORDER BY
startPos DESC

--Replace the replace string with last occurence of find string
SELECT SUBSTRING(@initialString,0,@startPos1) +@replaceString+ SUBSTRING(@initialString,@endPos1,@totalStringLength) as 'Result'

END
GO

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:

exec sp_SplitToGetLastOccurence 'is hello is is world this world is so beautiful is','is','YES'

Step 3: The Output will be as shown below:

Step 1: Below is the query that we can use in order to convert the date into words:

declare @date datetime = '01-10-1985'--Input DateOfBirth

declare @yr smallint = datepart(year, @date);
declare @month smallint= datepart(month, @date);
declare @day smallint = datepart(day, @date);
declare @word varchar(2000)

declare @NumTable table(NumId int, Name varchar(50))

insert into @NumTable
values(1,'First'),
(2,'Second'),(3,'Third'),(4, 'Fourth'),(5,'Five'), (6,'Six'),
(7,'Seventh'),(8,'Eighth'),(9,'Nineth'),(10,'Ten'),(11,'Eleven'),
(12,'Twelve'),(13,'Thirteen'),(14,'Fourteen'),(15,'Fifteen'),(16,'Sixteen'),
(17,'Seventeen'),(18,'Eighteen'),(19,'Nineteen'), (20,'Twenty'),(21,'TwentyFirst'),
(22,'Twenty Second'),(23,'Twenty Third'),(24,'Twenty Fourth'),(25,'Twenty Five'),
(26,'Twenty Six'), (27,'Twenty Seven'),(28,'Twnety Eight'),(29, 'Twenty Nine'),
(30,'Thirty'),(31, 'Thirty First'),(32,'Thrity Two'),(33,'Thrity three'),(34,'Thirty Four'),
(35,'Thrity Five'),(36,'Thiry Six'),(37,'Thirty Seven'),(38,'thrity eight'),(39,'Thirty Nine'),(40,'Forty'),
(41,'Forty one'), (42,'Forty two'),(43,'Forty three'),(44,'Forty four'),(45,'Forty Five'),(46,'Forty Six'),(47,'Forty Seven'),
(48,'Forty eight'),(49,'Forty nine'),(50,'fifty'),(51,'Fifty one'),(51,'Fifty one'),(52,'Fifty two'),(53,'Fifty three'),
(54,'Fifty four'),(55,'Fifty five'),(56,'Fifty six'),(57,'fifty seven'),(58,'fifty eight'),(59,'fifty nine'),
(60,'Sixty'),(61,'sixty one'),(62,'sixty two'),(63,'sixty three'),(64,'sixty four'),(65,'sixty five'),(66,'sixty six'),(67,'sixty seven'),
(68,'sixty eight'),(69,'sixty nine'),(70,'seventy'),(71,'seventy one'),(72, 'seventy two'),(73,'seventy three'),
(74,'seventy four'),(75,'seventy five'),(76,'seventy six'),(77,'seventy seven'),(78,'seventy eight'),(79,'seventy nine'),
(80,'eighty'),(81,'eighty one'),(82,'eighty two'),(83,'eighty three'),(84,'eighty four'),(85,'eight five'),(86,'eight six'),
(87,'eight seven'),(88,'eighty eight'),(89,'eighty nine'),(90,'ninety'),(91,'ninety one'),(91,'ninety one'),
(92,'ninety two'),(93,'ninety three'),(94,'ninety four'),(95,'ninety four'),(95,'ninety five'),(96,'ninety six'),
(97,'ninety seven'),(98,'ninety eight'),(99,'ninety nine')

-- Day
select @word = name
from @NumTable where NumId = @day

set @word = @word + ' '
-- Month
select @word = @word + DATENAME(month,@date)

set @word = @word + ' '

-- Year
select @word = @word + name
from @NumTable where NumId = left(@yr,2)

set @word = @word + ' '

select @word = @word + name
from @NumTable where NumId = Right(@yr,2)

select @word as 'DOB_in_Words'

Sql-Server-Query-World

Step 1: Let's analyse the input first which is as shown below:

Step 2: Now lets write a query that would help us inorder to get the columns into rows as shown below:

Step 3: The output will be as shown below:

1st Approach:

2nd Approach:

3rd Approach:

Today I will show you how we can write a query in order to convert Binary to Decimal in SQL Server and vice-versa:

Code Formatting in Stored Procedure on any version of SQL Server:

Indenting

To choose an indenting style:

To change indent tab settings:

To indent code:

To unindent code:

To convert white space to tabs:

To convert tabs to spaces:

Converting Text to Upper and Lower Case

To switch text to upper or lower case:

Displaying and Linking to URLs

To display a clickable URL:

Step 1: Create a table

Step 2: Insert values into the table created in Step 1

Step 3: Below is the query to get multiple rows data in a single row and in a single column

Step 4: The output of the query would be as below:

Conclusion:

Conclusion:

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 1: Below is the query that we can use in order to convert the date into words:

Step 2: The output is as shown below:

Followers

Popular Posts

Labels

About Me