Ms Sql'de Pivot Kavramı,Karar Yapıları,Temp Table( Geçici Tablolar) ve Bulk Copy Kullanımı

Sizinle paylaşacağım örneklerde “AdventureWorks” üzerinde çalışacağım. Veritabanı sunucusu olarak da SQL 2012’i kullanıyor olacağım.

  --pivot kavrami:
  --genelde raporlamada kullanılan bir tekniktir.Yaptığı iş: satır halinde gösterilen verileri,sorun haline
  --cevirmektedir.(Sadece  görüntülemede)

     create table Siparis
     (
       SiparisId int identity(1,1),
       MusteriAdSoyad nvarchar(50),
       UrunAdi nvarchar(50),
       Tutar decimal,
       Donem int
      )

      --veri ekliyelim

      insert into Siparis(MusteriAdSoyad,UrunAdi,Tutar,Donem)
      values('Refik Saritepe','Apple Ipad',1000,201001)

      insert into Siparis(MusteriAdSoyad,UrunAdi,Tutar,Donem)
      values('Adnan Yusuf','lg Notebook',950,200912)

      insert into Siparis(MusteriAdSoyad,UrunAdi,Tutar,Donem)
      values('Bahadir Erdem','PackerdBell Notebook',1200,200911)

      insert into Siparis(MusteriAdSoyad,UrunAdi,Tutar,Donem)
      values('Refik Saritepe','Nikon D500',1480,200912)

      insert into Siparis(MusteriAdSoyad,UrunAdi,Tutar,Donem)
      values('Refik Saritepe','Samsung omnia',570,200912)

      insert into Siparis(MusteriAdSoyad,UrunAdi,Tutar,Donem)
      values('Refik Saritepe','HP Printer',250,200912)

      insert into Siparis(MusteriAdSoyad,UrunAdi,Tutar,Donem)
      values('Bahadir Erdem','HP printer',2100,200912)

      insert into Siparis(MusteriAdSoyad,UrunAdi,Tutar,Donem)
      values('Bahadir Erdem','ADSL MODEM',400,200911)

      insert into Siparis(MusteriAdSoyad,UrunAdi,Tutar,Donem)
      values('Adnan YUsuf ','Lg telefon',800,201011)

      insert into Siparis(MusteriAdSoyad,UrunAdi,Tutar,Donem)
      values('Bahadir Erdem','Hp Printer',2100,200912)

      --müsteri basina aylık ne kadar harcama yapildi

      select MusteriAdSoyad,Donem,SUM(tutar) 'Harcama tutar,' from Siparis
      group by  MusteriAdSoyad,Donem

      select * from Siparis

      ---pivot ile satirları sutun haine donuşturelim
      select *
       from (select
             MusteriAdSoyad,
             Donem,
             SUM(Tutar) as ToplamTutar

              from Siparis
              group by MusteriAdSoyad,Donem
              ) as gTablo
              Pivot
              (
               sum(ToplamTutar)
               for Donem in ([200911],[200912],[201001])
               )
               as p
    --- Hangi ayda kaç kişi doğmuş

   select *from
   (
    select DATENAME(YY,BirthDate)[Yil],DATENAME(MM,BirthDate)[Ay],
    COUNT(*) as ToplamKisi from HumanResources.Employee
    group by  DATENAME(MM,BirthDate),DATENAME(YY,BirthDate)
  )
    as gTable
      PIVOT
  (
	Sum(ToplamKisi)
	for [Ay]
	in
	([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])
   )
    as p


   --SalesOrderDetail,salesORderHeader tablolarını kullanarak,aylik olarak yıl bazında toplam
   --satış miktarlarını gösterlim.
     SELECT Yil,[1][Ocak],[2][Şubat],[3][Mart],[4][Nisan],[5][Mayıs],[6][Haziran],
                [7][Temmuz],[8][Ağustos],[9][Eylül],[10][Ekim],[11][Kasım],[12][Aralık]
     FROM
    (
    SELECT YEAR(OrderDate)Yil,MONTH(OrderDate) Ay,SUM(LineTotal) Toplam FROM Sales.SalesOrderHeader SOH
    JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID=SOD.SalesOrderID
    GROUP BY YEAR(OrderDate),MONTH(OrderDate)
    ) Tablo
    Pivot
    (
      SUM(Toplam)
      FOR Ay IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) [PivotTablo]

    ORDER BY Yil

--2Yöntem
  select * from
  (
  select DATENAME(YY,Orderdate)'Yil' ,Datename(mm,orderDate)'ay', Sum(Linetotal)
   'toplamsatis'
    from Sales.SalesOrderHeader SOH
    JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID=SOD.SalesOrderID
    group by DATENAME(YY,OrderDate),DATENAME(MM,OrderDate)
   ) as Tablo
    PIVOT
    (
        SUM(ToplamSatis)
        for [Ay]


        	in
	    ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])
        )
       as p

---Karar Yapıları-->
--- if else if,else
--karar yapılarından case when then ifadeleri ile çalışmak

 declare @Gun int
 set  @Gun=11
 select
 case
 when @Gun=1 then 'Pazartesi'
 when @Gun=2 then 'Sali'
 when @Gun=3 then 'Çarşamba'
 when @Gun=4 then 'Perşembe'
 when @Gun=5 then 'Cuma'
 when @Gun=6 then 'Cumartesi'
 when @Gun=7 then 'Pazar'
  else 'Hatali secim'
  end as Gun
 ---when bildiriminde,koşul uyar ise then bloğundaki kodlar calistirilir.
 --Production.product tabloasundaki ürünlerin fiyatlarına bakip
 --0 ile 50 *  koyalım
 --50-100 **
 --100-200***
 --200-300****
 --300> çok büyük deger


  select Name,Color,
  case
  when listprice>=0 and listprice<50 then '*'
  when listprice>=50 and listprice<100 then '**'
  when listprice>=100 and listprice<200 then '***'
  when listprice>=200 and listprice<300 then '****'
  when listprice>=300  then 'çok büyük deger'
  end
     as Oran,ListPrice
     from Production.product order By ListPrice asc

     --HumanResources.Emoployee tablosundaki kişilerin MarialStatus ve Gender değerlerini bakip,
     --Bayan ve Evli
     --Erkek ve Evli
     --Bayan ve Bekar
     --Erkek ve Bekar

     select * from HumanResources.Employee

     select MaritalStatus,Gender,DATEdiff(YY,Birthdate,GETDATE())'Yas',
     case
     When  MaritalStatus='M' and Gender='M' then 'Evli ve Erkek'
     When  MaritalStatus='M' and Gender='F' then 'Evli ve Bayan'
     When  MaritalStatus='S' and Gender='M' then 'Bekar ve Erkek'
     When  MaritalStatus='S' and Gender='F' then 'Bekar ve Kadin'
     end
       as Durum
    from HumanResources.Employee emp
    inner join Person.Contact pc
    on  emp.ContactID=pc.ContactID

    --**************Temp table -- Geçici tablolar**************

    --veri tabanlarında kalıcı tablolar yapabildiğimiz gibi oturum kapandığında  silinen yada sunucu restart edildiğinde yok olan
    --tablolar yapabilmemiz mümkündür.

    --Temp tablolara neden ihtiyac duyarız:Örneğin çok büyük tablolar ile calisirken bu tablolar üzerinde işlem yaparken (Filtreleme
     --işlemleri) verileri kuculterek sorgulamak işimize gelebiliriz.Tabiki bu küçültme işlemlerinde küçülen ufak parçalarında
     --belli bir süre sonra silinmesi istenebilir,Böyle bir durumda temp tablolar bizim için çok önemli bi yere sahiptir.

      create table #GeciciTablo
      (
       Id int not null identity(1,1),
       Ad nvarchar(Max)
       )

       --Gecici tablolar başında diyez(#) konularak oluşturulur.

       --Gecici tabloumuzu insert yapalım

      insert into #GeciciTablo(Ad) values ('a')
      insert into #GeciciTablo(Ad) values ('b')
      insert into #GeciciTablo(Ad) values ('c')
      insert into #GeciciTablo(Ad) values ('d')
      insert into #GeciciTablo(Ad) values ('e')

    ---Sorgulayalım

     select * from #GeciciTablo

     --notlar : Bir gecici tyablo oluşturulduğunda bu oluşrulan gecici tabloyu tabloyuu yaratan user üzerine acilmiş bir tablodur,
     --Yani bir  user veri tabanı motoruna bağlandığı anda motor bağlanan bu user a bir Session açar(session  motorun kullanıcıyı
     --- tanıması icin gerekli yapar)
     ---*********************************Yazmadık**************************


       select * from #GeciciTablo

       ---Bulk Copy Kullanımı
       --sql server da bir tabloyunun içeriğini olduğu gibi başka bir tabloya kopyalamak için kullanılır

       select * into Urunler --yeni tablo oluşturulacak
       from Production.Product ---p.p tablosundaji tüm içerik aktarılacak

       --urunler isminde yeni bir tablo oluşturuldu

        select * from Urunler

        --yeni bir tablo yapalım
         create table NewUrunler
         (
           Ad nvarchar(Max),
           renk nvarchar (max),
           Fiyat money
           )

           --production.product tablosundaki tüm ürünlerin adlarını renklerini ve fiyatlarını yeni tablomuza kopyalıyalım
           insert into NewUrunler (Ad,renk,Fiyat)
           select name,ISNULL(Color,'Renksiz'),Listprice from Production.Product

           --bakalım
           select * from NewUrunler

           --bulk copy ile iki veri tabanı arasındaki  tablolardan koplama yapmakta  mümkündür.

           create database MyAdventureWorks
           --Adventurework veri tabanındaki,Production.product tablosu,
           --MyAdventureworks deki urun tablosuna eklensin

           select * into MyAdventureWorks.dbo.Urun
           from AdventureWorks.Production.Product

        --tumbilgiler diye bir tablo yaratıp bu tablo içerisinde kategoriadi,altkategoriadi,urunadi,UrunRengi urunfiyatı
        --bilgilerini aktaralım.

         create table TumBilgiler
         (
           Kategoriadi nvarchar(Max),
           altkategoriadi nvarchar (max),
           Urunadi nvarchar (max),
           UrunRengi nvarchar (max),
           UrunFiyati Money
           )

           insert into TumBilgiler (Kategoriadi,altkategoriadi,Urunadi,UrunRengi,UrunFiyati)
           select  ppc.Name,ppsc.Name,pp.name,pp.Color,pp.Listprice from Production.Product pp
           join Production.ProductSubcategory ppsc
           on ppsc.ProductSubcategoryID=pp.ProductSubcategoryID
           join Production.ProductCategory ppc
           on
           ppc.ProductCategoryID=ppsc.ProductCategoryID

            select * from TumBilgiler


         --Boyner diye bir veri tabanı oluşturulup,
         --içerisine categories,products,personel,user,sale tablolarını oluşrulup(ilişkiler düşünülecek)
         --Bu tablolara insert update delete ve sorgulama yapan procedureler yazalım

          select * from  Production.Product
          select * from  Production.ProductSubcategory
          select * from  Production. ProductCategory
          select * from HumanResources.Employee

          create database Boyner
           use Boyner

create table Product
(
ProductId int identity(100,1),
Name nvarchar(max),
ProductCategoryId int,
Color nvarchar(max),
ProductionPrice money
)

create table Catagories
(
ProductCategoryId int identity(1,1),
ProductCategory nvarchar(max)
)
create table Personel
(
PersonelId int identity(1,1),
Name nvarchar(max),
Surname nvarchar(max),
Title nvarchar(max),
Birthdate datetime,
MaritalStatus nchar(1),--M,S
Gender nchar(1),--M,F
HireDate datetime
)
create table PersonelContact
(
PersonelId int,
Email nvarchar(max),
Telephone nvarchar(11),
Adress nvarchar(max)
)
create table Sale
(
ProductId int,
ProductCategoryId int,
StockLevel int,
ListPrice money,
SaleQuantity int,
CustomerId int
)
create table Customer
(
CustomerId int identity(50,15),
CustomerName nvarchar(max),
CustomerSurname nvarchar(max),
Email nvarchar(max),
Telephone nvarchar(11),
Adress nvarchar(max)
)


execute sp_CategoryKayit 'Bikes'
execute sp_ProductKayit 'Mountain Bike','Red',230
execute sp_PersonelKayit 'İsmail','Genç','Müdür','12/12/1955','M','M','test@hotmail.com',
'22347169283','avcılar'



select *from Product
select *from Personel
select *from PersonelContact
select *from Sale
select *from Customer
select *from Catagories





truncate table Catagories
truncate table Product
truncate table Personel
truncate table PersonelContact



create procedure sp_ProductIdGetir
as
begin
	return (select top 1 ProductId from Product order by ProductId desc)
end

create procedure sp_CategoryIdGetir
as
begin
	return (select top 1 ProductCategoryId from Catagories order by ProductCategoryId desc)
end

create procedure sp_PersonelIdGetir
as
begin
	return(select top 1 PersonelId from Personel order by PersonelId desc)
end

create procedure sp_CustomerIdGetir
as
begin
	return(select Top 1 CustomerId from Customer order by CustomerId desc)
end

--create procedure sp_UrunKayit
--(
--@Name nvarchar(max),
--@Color nvarchar(max),
--@ProductionPrice money,
--@ProductCategory nvarchar(max),
--@ProductId int,
--@ProductCategoryId int,
--@StockLevel int,
--@ListPrice money,
--@SaleQuantity int,
--@CustomerId int
--)


create procedure sp_CategoryKayit
(
@ProductCategory nvarchar(max)
)
as
	begin
	insert into Catagories (ProductCategory)
	values (@ProductCategory)
	return @@IDENTITY
	end


create procedure sp_ProductKayit
(
@Name nvarchar(max),
@Color nvarchar(max),
@ProductionPrice money
)
as
	begin
		declare @ProductCategoryId int
		execute @ProductCategoryId=sp_CategoryIdGetir
		insert into Product (Name,ProductCategoryId,Color,ProductionPrice)
		values
		(@Name,@ProductCategoryId,@Color,@ProductionPrice)
		return @@IDENTITY
		end



create procedure sp_PersonelKayit
(
--PersonelId int identity(1,1),
@Name nvarchar(max),
@Surname nvarchar(max),
@Title nvarchar(max),
@Birthdate datetime,
@MaritalStatus nchar(1),--M,S
@Gender nchar(1),--M,F
@Email nvarchar(max),
@Telephone nvarchar(11),
@Adress nvarchar(max)
--HireDate datetime
)
as
	begin
	declare @LastId int
	declare @HireDate datetime
	set @HireDate=GetDate()
		insert into Personel(Name,Surname,Title,Birthdate,MaritalStatus,Gender,HireDate)
		values
		(@Name,@Surname,@Title,@Birthdate,@MaritalStatus,@Gender,@HireDate)
		set @LastId = @@IDENTITY
		insert into PersonelContact(PersonelId,Email,Telephone,Adress)
		values
		(@LastId,@Email,@Telephone,@Adress)
		return @LastId
		end

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

--pivot kavrami:

--genelde raporlamada kullanılan bir tekniktir.Yaptığı iş: satır halinde gösterilen verileri,sorun haline

--cevirmektedir.(Sadece görüntülemede)

create table Siparis

(

SiparisId int identity(1,1),

MusteriAdSoyad nvarchar(50),

UrunAdi nvarchar(50),

Tutar decimal,

Donem int

)

--veri ekliyelim