单行数据变多行

创建测试表并插入数据

IF OBJECT_ID('mutitb') >0 
DROP TABLE mutitb


CREATE TABLE [dbo].[mutitb](
    [id] [INT] IDENTITY(1,1) NOT NULL,
    [FirstName] [VARCHAR](10) NULL,
    [LastName] [VARCHAR](10) NULL,
 CONSTRAINT [PK_mutitb] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


INSERT dbo.mutitb
        ( FirstName, LastName )
VALUES  ( 
          'Anna', -- FirstName - varchar(10)
          'Gates'  -- LastName - varchar(10)
          )
          
          INSERT dbo.mutitb
        ( FirstName, LastName )
VALUES  ( 
          'John', -- FirstName - varchar(10)
          'Doe'  -- LastName - varchar(10)
          )
          
                    INSERT dbo.mutitb
        ( FirstName, LastName )
VALUES  ( 
          'Joe', -- FirstName - varchar(10)
          'Bloggs'  -- LastName - varchar(10)
          )
          
                    INSERT dbo.mutitb
        ( FirstName, LastName )
VALUES  ( 
          'Raj', -- FirstName - varchar(10)
          'Kumar'  -- LastName - varchar(10)
          )

1.Cross Join

CREATE TABLE #temp1
    (
      item VARCHAR(10) ,
      itemdescription VARCHAR(20)
    )

INSERT  #temp1
        ( item, itemdescription )
VALUES  ( '1', -- item - varchar(10)
          'item1'  -- itemdescription - varchar(20)
          )
          
INSERT  #temp1
        ( item, itemdescription )
VALUES  ( '2', -- item - varchar(10)
          'item2'  -- itemdescription - varchar(20)
          )        

INSERT  #temp1
        ( item, itemdescription )
VALUES  ( '3', -- item - varchar(10)
          'item3'  -- itemdescription - varchar(20)
          )               
    
SELECT  *
FROM    mutitb
        CROSS JOIN #temp1
ORDER BY firstName
     
      
DROP TABLE #temp1        

2.Union all

 

3.with