T-SQL Challenge

I think this challenge will be very interesting as you need to sort the data horizontally and vertically to solve this. To understand this challenge better, let us look at the source data and expected result.

Source Data

C1   C2   C3

---- ---- ----

2    1    3

3    2    1

Z    X    Y

B    C    D

Y    Z    X

B    C    A

Expected Result

Here is the expected output. Write a single query that operates on the above data and produces the output given below.

C1   C2   C3

---- ---- ----

1    2    3

A    B    C

B    C    D

X    Y    Z

This challenge requires the following steps:

1. Sort the values horizontally. Arrange the values from smallest to the largest. for example, the first row contains values “2”, “1” and “3”. This should be arranged as “1”, “2” and “3”.
2. Sort the rows vertically. This is the regular sorting that we are familiar with. Final result should be sorted as shown in the ‘expected result’ listing.
3. Remove duplicates – Duplicate rows should be removed from the final output.

Sample Data

You can use the following script to generate the sample data.

DECLARE @t TABLE (

 c1 CHAR(1),

 c2 CHAR(1),

 c3 CHAR(1)

)



insert into @t (c1, c2, c3) values ('2','1','3')

insert into @t (c1, c2, c3) values ('3','2','1')

insert into @t (c1, c2, c3) values ('Z','X','Y')

insert into @t (c1, c2, c3) values ('B','C','D')

insert into @t (c1, c2, c3) values ('Y','Z','X')

insert into @t (c1, c2, c3) values ('B','C','A')



SELECT * FROM @t

Notes

1. As usual, write a single query that produces the expected output. CTEs are acceptable.

Solution :

DECLARE @t TABLE (

 c1 CHAR(1),

 c2 CHAR(1),

 c3 CHAR(1)

)



insert into @t (c1, c2, c3) values ('2','1','3')

insert into @t (c1, c2, c3) values ('3','2','1')

insert into @t (c1, c2, c3) values ('Z','X','Y')

insert into @t (c1, c2, c3) values ('B','C','D')

insert into @t (c1, c2, c3) values ('Y','Z','X')

insert into @t (c1, c2, c3) values ('B','C','A')

 
SELECT DISTINCT CASE WHEN c1<c2 AND c1<c3 THEN c1 
    WHEN c2<c1 AND c2<c3 THEN c2 ELSE c3 END C1, 
CASE WHEN C1>C2 AND C1<C3 THEN C1 WHEN C1<C2 AND C1>C3 THEN C1  
     WHEN C2>C1 AND C2<C3 THEN C2 WHEN C2<C1 AND C2>C3 THEN C2  
     ELSE C3 END C2, 
CASE WHEN c1>c2 AND c1>c3 THEN c1 WHEN c2>c1 AND c2>c3 THEN c2  
     ELSE c3 END C3
FROM @t

To Generate Table Structure in Excel Sheet

This blog help the people who want to generate a excel sheet of the all table structure for specified database. Following is the procedure which generate a Excel sheet in your D drive for all table structure of specified database.

Before Executing this procedure you have to follow following settings.

1).Microsoft SQL Server 2005 -> Configuration Tools -> Surface Area Configuration -> Surface Area Configuration for Features

2) Mark True on Enable Web Assistant.

Or

Run Following query in sql server which will enable web assistant procedures.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Web Assistant Procedures', 1;

GO

RECONFIGURE

GO

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[SP_CREATE_EXCEL_STRUCTURE_FOR_ALLDATABASE]

(

@db_name VARCHAR(100)

)

AS

BEGIN

DECLARE @columns VARCHAR(8000), @sql VARCHAR(8000), @data_file VARCHAR(100)

DECLARE @file_name varchar(100),@table_name VARCHAR(100)

DECLARE @Constraint VARCHAR(50)

DECLARE C1 CURSOR FOR select name from sys.tables

OPEN C1

FETCH NEXT FROM C1 INTO @table_name

WHILE @@FETCH_STATUS=0

BEGIN

drop TABLE MYTMP

SELECT @Constraint=B.COLUMN_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A,

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B

WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

AND A.TABLE_NAME=@table_name

SELECT column_name FieldName,Data_type DataType,

character_maximum_length Size,is_nullable AllowNulls

INTO MYTMP FROM information_schema.columns

WHERE table_name=@table_name

ALTER TABLE MYTMP ADD PrimaryKey VARCHAR(20)

ALTER TABLE MYTMP ADD ForeignKey VARCHAR(70)

ALTER TABLE MYTMP ADD AutoIncrement VARCHAR(20)

ALTER TABLE MYTMP ADD DefaultValue VARCHAR(50)

ALTER TABLE MYTMP ADD Description VARCHAR(500)

UPDATE MYTMP SET PrimaryKey='YES' WHERE FieldName=@Constraint

UPDATE MYTMP SET PrimaryKey='' WHERE FieldName<>@Constraint

---=================To Add Foreign key=================================

UPDATE MYTMP SET ForeignKey=Tmp.ForeignKey

From

(SELECT COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,fc.referenced_column_id)

AS ReferenceColumnName,

OBJECT_NAME (f.referenced_object_id)+

'('+COL_NAME(fc.referenced_object_id,fc.referenced_column_id)+')'

As ForeignKey

FROM sys.foreign_keys AS f

INNER JOIN sys.foreign_key_columns AS fc ON

f.OBJECT_ID = fc.constraint_object_id

WHERE OBJECT_NAME(f.parent_object_id)=@TABLE_NAME)As Tmp

WHERE FieldName=Tmp.ColumnName

UPDATE MYTMP SET ForeignKey='' WHERE ForeignKey IS NULL

----======================To Set Default Value===============================

UPDATE MYTMP SET DefaultValue=Tmp.TEXT

FROM

(SELECT name,REPLACE(REPLACE(TEXT,'(',''),')','')TEXT FROM syscolumns

inner join syscomments on(syscomments.id=syscolumns.cdefault)

WHERE syscolumns.id = object_id(@Table_name) AND cdefault > 0)As Tmp

WHERE FieldName=Tmp.Name

UPDATE MYTMP SET DefaultValue='' WHERE DefaultValue IS NULL

----======================= To set Auto Increment============================

UPDATE MYTMP SET AutoIncrement='YES'

FROM

(SELECT NAME AS COLUMNNAME FROM SYS.IDENTITY_COLUMNS

WHERE OBJECT_NAME(OBJECT_ID)=@Table_name)AS TMP

WHERE FieldName=Tmp.ColumnName

UPDATE MYTMP SET AutoIncrement='' WHERE AutoIncrement IS NULL

----========================To Set Description ==============================

UPDATE MYTMP SET Description=Tmp.Description

FROM

(SELECT convert(varchar(500),ep.value) AS Description,c.name AS ColumnName

FROM sys.objects o

INNER JOIN sys.extended_properties ep ON o.object_id = ep.major_id

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

LEFT JOIN syscolumns c ON ep.minor_id = c.colid AND ep.major_id = c.id

WHERE o.type='U' and o.name=@table_name)As Tmp

Where FieldName=Tmp.ColumnName

UPDATE MYTMP SET Description='' WHERE Description IS NULL

---===============================================================

SET @file_name='D:\'+@table_name+'.xls'

--===================================

exec sys.sp_makewebtask @outputfile = @file_name,

@query = 'Select * from mytmp',

@colheaders =1,

@FixedFont=0,@lastupdated=1,@resultstitle=@table_name,

@dbname =@db_name

--========================================

FETCH NEXT FROM C1 INTO @table_name

END

CLOSE C1

DEALLOCATE C1

END

------------------TO EXECUTE

--SP_CREATE_EXCEL_STRUCTURE_FOR_ALLDATABASE 'jito'