What are the challenges you face when working across database platforms? Take the survey
Options

Sample SQL Code

Sample code used to create some tables for the missing report.

CREATE TABLE ConstosoProduct<br>(ProductID INT IDENTITY(1,1) NOT NULL CONSTRAINT ContosoProductPK PRIMARY KEY<br>, ProductLevel1 VARCHAR(100)<br>, ProductLevel2 VARCHAR(100)<br>, ProductLevel3 VARCHAR(100)<br>, ProductName VARCHAR(100)<br>)<br>GO<br>INSERT dbo.ConstosoProduct<br>&nbsp;&nbsp;&nbsp; (ProductLevel1<br>&nbsp; , ProductLevel2<br>&nbsp; , ProductLevel3<br>&nbsp; , ProductName)<br>&nbsp; VALUES<br>&nbsp; ( 'Audio', 'MP4&MP3', '', ''),<br>&nbsp; ( 'Audio', 'Recorder', '', ''),<br>&nbsp; ( 'Audio', 'Radio', '', ''),<br>&nbsp; ( 'Audio', 'Recording Pen', '', ''),<br>&nbsp; ( 'TV and Video', 'Televisions', '', ''),<br>&nbsp; ( 'TV and Video', 'VCD & DVD', '', ''),<br>&nbsp; ( 'TV and Video', 'Home Theater System', '', ''),<br>&nbsp; ( 'Computers', 'Laptops', '', 'Fabrikam Laptop19W M9800 Black'),<br>&nbsp; ( 'Computers', 'Desktops', '', 'Adventure Works Desktop PC2.30 MD230 Silver')
<div><br></div>CREATE TABLE UserSample<br>(&nbsp;&nbsp; UserName&nbsp;&nbsp; VARCHAR(100)<br>&nbsp; , OrgLevel1 VARCHAR(20)<br>&nbsp; , OrgLevel2 VARCHAR(20)<br>&nbsp; , OrgLevel3 VARCHAR(20));<br>GO<br><br>INSERT dbo.UserSample<br>&nbsp;&nbsp;&nbsp; (UserName<br>&nbsp; , OrgLevel1<br>&nbsp; , OrgLevel2<br>&nbsp; , OrgLevel3)<br>VALUES<br>&nbsp;&nbsp;&nbsp; ('User g', 'someOrg', 'other Org', null),<br>&nbsp;&nbsp;&nbsp; ('User B', 'someOrg', 'other Org', 'thirdOrg'),<br>&nbsp;&nbsp;&nbsp; ('User C', 'someOrg', '', 'thirdOrg'),<br>&nbsp;&nbsp;&nbsp; ('User D', '', 'other Org', 'thirdOrg'),<br>&nbsp;&nbsp;&nbsp; ('User E', '', 'other Org', ''),<br>&nbsp;&nbsp;&nbsp; ('User F', '', 'other Org', '')


CREATE TABLE [dbo].[ProductSample](<br>&nbsp;&nbsp;&nbsp; [ProductName] [varchar](100) NULL,<br>&nbsp;&nbsp;&nbsp; [ProductLevel1] [varchar](20) NULL,<br>&nbsp;&nbsp;&nbsp; [ProductLevel2] [varchar](20) NULL,<br>&nbsp;&nbsp;&nbsp; [ProductLevel3] [varchar](20) NULL<br>) ON [PRIMARY]<br>GO<br>INSERT INTO dbo.ProductSample<br>&nbsp;&nbsp;&nbsp; (ProductName<br>&nbsp; , ProductLevel1<br>&nbsp; , ProductLevel2<br>&nbsp; , ProductLevel3)<br>VALUES<br>( 'Product A', 'somecat', 'other cat', 'thirdcat' ), <br>( 'Product B', 'somecat', 'other cat', 'thirdcat' ), <br>( 'Product C', 'somecat', '', 'thirdcat' ), <br>( 'Product D', '', 'other cat', 'thirdcat' ), <br>( 'Product E', '', 'other cat', '' ), <br>( 'Product F', '', 'other cat', '' )<br>





Tagged:

Comments

Sign In or Register to comment.