如何在一行中显示与单个clent相关的各种属性 [英] how to display various attribute related to single clent in a single row

问题描述

您好，这是我的数据库设计，它由以下3个表ved，产品和品牌组成.他们的定义如下

hi here is my database design which consist of following 3 tables vedor,products and brands. their def are as follows

vendor(
vid int primary key identity,
v_name varchar(50),            -- name of vendor
v_producttype varchar(50)      -- type of product eg appreals or non-appreals etc
);

products(
p_id int primary key identity,
vref_id int foreign key references vendor(vid), -- refers to vid of vendor
produts varchar(50)                             -- refers to products
)

brands(
b_id int primary key identity
vref_id int foreign key references vendor(vid), -- refers to vid of vendor
brands varchar(50)                              -- refers to various brands
) 

现在可以看到一个供应商可以拥有许多产品以及许多品牌
我要做的是在(虚拟)表的单个单元格中显示所有产品以及属于特定供应商的品牌，在该表中显示vendro详细信息

如何做到这一点.我曾尝试使用sql equi-joins，但每次为每种产品重复行.我应该怎么使用任何一种功能.我在sql函数中不擅长

请指导我

now as one can see one vendors can have many products as well as many brands
what i want to do is to display all products as well as brands belonging to particular vendor in a single cell of a (dumy) table where vendro details are displayed

how to this. i have tried using sql equi-joins but every time rows get repeated for every products. what should i do use any kind of function. i am not good in sql functions

please guide me

推荐答案

听起来好像您要将这些行连接到一个单元格中.要做到这一点

It sounds like you want to concatenate the rows into a single cell. To do that do

DECLARE @RowsIntoCell NVARCHAR (MAX); 
SELECT @RowsIntoCell = COALESCE(@RowsIntoCell+ ',', '') + ColumnThatHasValues
FROM YourTable 

这将从列(ColumnThatHasValues)中获取所有值，并用逗号将它们定界到名为@RowsIntoCell

This will take all values from a column (ColumnThatHasValues) and comma delimit them into a variable named @RowsIntoCell

这篇关于如何在一行中显示与单个clent相关的各种属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！