如何通过对象ID和列ID查询表数据? [英] How to query table data by Object Id and Column Id?
本文介绍了如何通过对象ID和列ID查询表数据?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
拥有表 Clients
。
PK姓氏名称地址
$ p>
1维达尔·阿图罗(Vidal Arturo)街...
2拉韦齐·埃塞奎尔(Lavezzi Ezequiel)街....
3库拉多(Guadrado Guillermo)街....
我想获取:
通过以下查询可以给我前四列,但如何将其与表数据链接?
SELECT TAB.object_id OBEJCTID,TAB.name TABLENAME,COL.column_id COLUMNID,COL.name FROM sys.tables TAB
JOIN SYS.columns COL
ON TAB.object_id = COL.object_id
WHERE TAB.object_id = 25659888;
解决方案您需要
unpivot
数据。
尝试类似的操作
;与cte
AS(SELECT column_name,
table_value
FROM clients
CROSS apply(VALUES('pk',CONVERT(varchar(20),PK)),
('LastName',lastname),
('Name',NAME),
('Address',address))cs(column_name,table_value)),
您的查询
AS(SELECT TAB.object_id OBEJCTID,
TAB.NAME TABLENAME,
COL.column_id COLUMNID,
COL.NAME
FROM sys.tables TAB
JOIN sys .columns COL
在TAB.object_id = COL.object_id
WHERE TAB.object_id = 25659888)
选择*
从cte c
加入yourquery y
ON y.NAME = c.column_name
Having the table
Clients
.PK LastName Name Address 1 Vidal Arturo St.... 2 Lavezzi Ezequiel St.... 3 Cuadrado Guillermo St....
I want to get:
With the following query gives me the first four columns but how can I link this with the table data?
SELECT TAB.object_id OBEJCTID, TAB.name TABLENAME, COL.column_id COLUMNID, COL.name FROM sys.tables TAB JOIN SYS.columns COL ON TAB.object_id = COL.object_id WHERE TAB.object_id = 25659888;
解决方案You need to
unpivot
the data.Try something like this
;WITH cte AS (SELECT column_name, table_value FROM clients CROSS apply (VALUES ('pk',CONVERT(varchar(20),PK)), ('LastName',lastname), ('Name', NAME), ('Address',address)) cs(column_name, table_value)), yourquery AS (SELECT TAB.object_id OBEJCTID, TAB.NAME TABLENAME, COL.column_id COLUMNID, COL.NAME FROM sys.tables TAB JOIN sys.columns COL ON TAB.object_id = COL.object_id WHERE TAB.object_id = 25659888) SELECT * FROM cte c JOIN yourquery y ON y.NAME = c.column_name
这篇关于如何通过对象ID和列ID查询表数据?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文