数据库列转行问题。
A表格式
B表格式
如何将A表的数据格式写成和到B表同格式。
目的：方便将A表数据插入到B 表中。
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-17 12:35:26
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([Id] varchar(2),[Name] varchar(2),[UserId] varchar(2),[DId] varchar(2),[Der] varchar(2),[DDate] varchar(2),[VId] varchar(2),[Ver] varchar(2),[VDate] varchar(2))
insert [A]
select '01','aa','bb','cc','dd','ee','ff','gg','hh'
--------------开始查询--------------------------
SELECT RIGHT('0'+cast(ROW_NUMBER()OVER(ORDER BY GETDATE()) AS varchar),2)id,name,userid,[type],typeid,typer,typedate
FROM (
select name,userid,'D' [type],did typeid,der typer,ddate typedate
from [A]
UNION ALL
select name,userid,'V' [type],vid typeid,ver typer,vdate typedate
from [A])a
----------------结果----------------------------
/*
id name userid type typeid typer typedate
---- ---- ------ ---- ------ ----- --------
01 aa bb D cc dd ee
02 aa bb V ff gg hh
*/
