Pivot and Unpivot

-- Creating Test Table
CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO
-- Selecting and checking entires in table
SELECT * FROM Product
GO-- Pivot Table ordered by PRODUCT
SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT
GO
-- Pivot Table ordered by CUST
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt
ORDER BY CUST
GO
-- Unpivot Table ordered by CUST
SELECT CUST, PRODUCT, QTY
FROM
(
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT
( SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt) p
UNPIVOT
(QTY FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)
) AS Unpvt
GO
-- Clean up database
DROP TABLE Product
GO

Drop Constraint in a table

Drop Constraint in a table

ALTER TABLE  [tblexternalload]

DROP CONSTRAINT [DF_tblexternalload_last_modifydate]

--
--

Regards,

Basheer Ahamed

 

Read XML Dicom Tag...

Read XML Dicom Tag..Using Sql Server..

<dicom>

  <attr tag="00020001" vr="OB" len="2">00\01</attr>

  <attr tag="00020002" vr="UI" len="22">1.2.840.10008.5.1.4.33</attr>

  <attr tag="00020003" vr="UI" len="44">1.2.276.0.26.1.1.1.2.2011.294.36813.5389133</attr>

  <attr tag="00020010" vr="UI" len="20">1.2.840.10008.1.2.1</attr>

  <attr tag="00020012" vr="UI" len="16">1.2.40.0.13.1.1</attr>

  <attr tag="00020013" vr="SH" len="12">dcm4che-2.0</attr>

  <attr tag="00080060" vr="CS" len="2">US</attr>

  <attr tag="00081032" vr="SQ" len="0" />

  <attr tag="00081120" vr="SQ" len="0" />

  <attr tag="00100010" vr="PN" len="6">test2</attr>

  <attr tag="00100020" vr="LO" len="16">15964-11-09-15-6</attr>

  <attr tag="00100030" vr="DA" len="8">19890909</attr>

  <attr tag="00100040" vr="CS" len="2">M</attr>

  <attr tag="00200010" vr="SH" len="2">14</attr>

  <attr tag="00400241" vr="AE" len="4">US63</attr>

  <attr tag="00400242" vr="SH" len="4">US63</attr>

  <attr tag="00400243" vr="SH" len="0" />

  <attr tag="00400244" vr="DA" len="8">20110915</attr>

  <attr tag="00400245" vr="TM" len="6">154333</attr>

  <attr tag="00400250" vr="DA" len="8">20110915</attr>

  <attr tag="00400251" vr="TM" len="6">154427</attr>

  <attr tag="00400252" vr="CS" len="10">COMPLETED</attr>

  <attr tag="00400253" vr="SH" len="2">15</attr>

  <attr tag="00400254" vr="LO" len="0" />

  <attr tag="00400255" vr="LO" len="0" />

  <attr tag="00400260" vr="SQ" len="0" />

  <attr tag="00400270" vr="SQ" len="-1">

    <item>

      <attr tag="00080050" vr="SH" len="0" />

      <attr tag="00081110" vr="SQ" len="0" />

      <attr tag="0020000D" vr="UI" len="46">1.2.276.0.26.1.1.1.2.2011.294.36813.3864399.1</attr>

      <attr tag="00321060" vr="LO" len="0" />

      <attr tag="00400007" vr="LO" len="0" />

      <attr tag="00400008" vr="SQ" len="0" />

      <attr tag="00400009" vr="SH" len="0" />

      <attr tag="00401001" vr="SH" len="0" />

    </item>

  </attr>

  <attr tag="00400281" vr="SQ" len="0" />

  <attr tag="00400340" vr="SQ" len="-1">

    <item>

      <attr tag="00080054" vr="AE" len="0" />

      <attr tag="0008103E" vr="LO" len="0" />

      <attr tag="00081050" vr="PN" len="0" />

      <attr tag="00081070" vr="PN" len="0" />

      <attr tag="00081140" vr="SQ" len="0" />

      <attr tag="00181030" vr="LO" len="8">Regular</attr>

      <attr tag="0020000E" vr="UI" len="46">1.2.276.0.26.1.1.1.2.2011.294.36867.1383130.1</attr>

      <attr tag="00400220" vr="SQ" len="0" />

    </item>

  </attr>

</dicom>

 

SQL QUERY:

 

DECLARE @doc xml                                                                                                         

Declare @id int

declare @idoc int

Declare @patienid as nvarchar(100)                                                                                                       

                                                                                                   

select @doc=DATA from TBLLOADDEAFULT       

 

select @doc

 

EXEC

sp_xml_preparedocument @idoc OUTPUT, @doc

 

SELECT

tag,len,comment

FROM

OPENXML(@idoc, '/dicom/attr')

WITH

(tag varchar(30)

,len varchar(30)

,comment ntext 'text()'

)

--
--

Regards,

Basheer Ahamed

Transfer column to row with coma separator

Transfer  column to row with coma separator

Declare @finalmsg as varchar(max)

SELECT

@finalmsg=

STUFF((

   SELECT ', ' +  cast(centerid as varchar(max)) from MedallWarehouse.dbo.TblCorpCenterMaster where Obsolute=0

  FOR XML PATH('') ), 1, 1, '')

  select @finalmsg

 

(column name)

 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 58, 59, 60, 61, 62