Sql Server Get Comma Separated list And Search Keyword

Sql Server Get Comma Separated list

SELECT empowner,
 SUBSTRING(
 (
  SELECT ( ' ' + dept)
  FROM Zo_Departments t2
  WHERE t1.empowner = t2.empowner
  ORDER BY t1.empowner, t2.empowner
  FOR XML PATH('')
 ), 3, 1000)
FROM Zo_Departments t1  where t1.empowner=2803
GROUP BY empowner

This data which looks like this

empowner  dept
2803 BS-SUPPORT
2803 E-SUPPORT
2803 UK-US-NZ
2803 APPLICATION-SUPPORT
2803 Pranav Ajgaonkar

will look like this

empowner (No column name)
2803 S-SUPPORT E-SUPPORT UK-US-NZ APPLICATION-SUPPORT Pranav Ajgaonkar

SELECT empowner,
 SUBSTRING(
 (
  SELECT ( ' ' + dept)
  FROM Zo_Departments t2
  WHERE t1.empowner = t2.empowner
  ORDER BY t1.empowner, t2.empowner
  FOR XML PATH('')
 ), 3, 1000),
(SELECT top 1 PATINDEX('%support%', dept) dept FROM Zo_Departments where Zo_Departments.empowner=t1.empowner) As EXCESS_BAGGAGE,
(SELECT top 1 PATINDEX('%support%', dept) dept FROM Zo_Departments where Zo_Departments.empowner=t1.empowner) As BAGGAGE,
(SELECT top 1 PATINDEX('%support%', dept) dept FROM Zo_Departments where Zo_Departments.empowner=t1.empowner) As EXCESS_BG,
(SELECT top 1 PATINDEX('%support%', dept) dept FROM Zo_Departments where Zo_Departments.empowner=t1.empowner) As EXCESS_LUGGAGE,
(SELECT top 1 PATINDEX('%support%', dept) dept FROM Zo_Departments where Zo_Departments.empowner=t1.empowner) As LUGGAGE
FROM Zo_Departments t1  where t1.empowner=2803
GROUP BY empowner

Share this:

Number of Days in a Month in SQL

Number of Days in a Month in SQL

select DAY(DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, '1-Jan-2012') + 1, 0)))

Share this:

Delete all tables in SQL

Delete all tables in SQL or drop multiple tables in sql

SELECT 'DROP TABLE '+table_name FROM Information_Schema.TABLES

Share this:

Import and Export Wizard with Identity Column

I  want to move data from one DB to another, while keeping the identity column values in the old DB. When I run the wizard I get a validation error "Failure inserting into the read-only column "UID".

Where UID is an identity column on that table. How can I override the identity functionality in order to be able to import the data?

Answer  : By Enable identity insert. There's a check box when you click the Edit Mappings.

Share this:

SQL Query – List Indexes

—- SQL Query – List Indexes

EXEC sp_helpindex 'tblgroups'

or

declare @tblName varchar(50);
set @tblName = 'tbltransaction';
SELECT     o.name as TableName, i.name AS IndexName
FROM         sysobjects o, sysindexes i
WHERE   (o.id = i.id and o.name = @tblName) AND (i.status = 18450 OR   i.status = 2097152)

Share this:

SQL Query – List Columns of table MS Database

---- SQL Query - List Columns of table MS Database
exec sp_columns tblMobileNumbers

Share this: