当前表数据已轮换 [英] Present table data rotated

问题描述

我想以逆时针方向旋转90度来显示下表，因此表的列名称字段将更改为类别类型，而对于每个类别，其他字段值将位于该类别以下.我如何为此编写Tsql以显示其他临时表中下表的结果.

i want to present the below table with a 90 degree rotate in the anti clock wise direction, so tables column name fields will change to category type and for each category the other field value will below that. How can i write Tsql for this to show result from below table in other temp table.

S.NO First_name      last_name   SignUpID  Rol_Name      EventDate category
1   User1Name   User1Name   12  90 Day Client   11/01/2011  DC
2   User1NameAtt1   User1NameAtt1   12  90 Day Client   11/01/2011  DC
3   User4Name   User4Name       13  Student Client  11/01/2011  ST
4   User4NameAtt1   User4NameAtt1   13  Student Client  11/01/2011  ST
5   user1           user1           14  90 Day Client   11/01/2011  DC
6   att1            att1            14  Employee-Acco   11/01/2011  AD
7   User4NameAtt2   User4NameAtt2   15  Student Client  11/01/2011  ST
8   easdsd          ertrt           15  90 Day Client   11/01/2011  DC
9   User4NameAtt3   User4NameAtt3   15  Employee-Acco   11/01/2011  AD

以上结果是我在运行一些复杂的SQL查询后得到的.
现在，我想以表格中的其他形式重新排列此结果集.
我将如何使用Tsql在下面介绍的表格中安排该结果集?我想要GroupBy(SignUpID)并想以表格的形式显示结果，该表格的形式是头的所有类别名称都来自上面的结果集中，并且低于该first_name + last_name的所有行中所有普通signUpId的名称，并在我想在同一行中为所有类别类型添加一个额外的列，以用于同一行中每个类别的计数.
我也在下面展示图片.

Above result i am getting after running some complex sql query.
Now i want to rearrange this resut set in other form in the table.
Using Tsql how can i arrange this result set in the form, i am presenting below? I want to GroupBy(SignUpID) and want to display result in a table in the form that all category name in the head for a pericular, from the above result set and below that first_name+last_name for all common signUpId in a row, and in the same row i want to add all category type with an additional column for the count of each category in the same row.
I am presenting a picture below too.

S.NO DC DC DC ST ST ST AD AD AD DC ST AD CA SP Asociate CA CA CA CA CA SPOUS SPOUS

我想为我的所有查询结果集都完全制成上述类型的表结构.每个字段都是上面一行中的一列.表中每种类别类型的一个区分列将对每一行中每种类别的类型进行计数.

我正在使用sql server 2005并尝试以上面显示的形式转换该表，但没有成功，请帮助我，如何进行查询以获取所需形式的结果，此后我将在临时状态下取该表表格，并从正面与我的网格视图绑定.如果还有其他最佳解决方案，请也建议我.

这是我为获取上述结果而做的事情，现在我要以我要问的表格的形式展示它.

I want to make exactly a table structure of above type for all my query result set. Each field is a column in above line. And one distinguish column for each category type in the the table will give count for each category type in each row.

I am using sql server 2005 and trying to convert that table in the form i am displaying above but no success, please help me out with this, how should i make query to fetch result of desire form, after which i will take that in temporary table and will bind with my grid view from the front hand. If there any other best solution please suggest me that too.

This is what i have done to fetch the above result, now i want to present it in the form i am asking you.

select Row_number() OVER(ORDER BY (SELECT 1)) AS 'Serial Number',
EP.FirstName,Ep.LastName, Ep.SignUpID, [dbo].[GetBookingRoleName](ES.UserId,EP.BookingRole) as RoleName,
(select top 1 convert(varchar(10),eventDate,103)from [3rdi_EventDates] where EventId=@ItemId) as EventDate,
(CASE [dbo].[GetBookingRoleName](ES.UserId,EP.BookingRole)
WHEN 'Employee - Marketing' THEN 'DC'
WHEN 'Employee - Accounting' THEN 'DC'
WHEN 'Coaches' THEN 'DC'
WHEN 'Student Client' THEN 'ST'
WHEN 'Guest Doctor' THEN 'GDC'
WHEN 'Guest Student' THEN 'GST'
WHEN 'Elite Client' THEN 'DC'
WHEN 'Advanced Client' THEN 'DC'
WHEN 'Maintenance Client' THEN 'DC'
WHEN 'Association Client' THEN 'AD'
WHEN 'MLHC In Training' THEN 'DC'
WHEN 'Employee - Coaching' THEN 'DC'
WHEN 'Employee - Students' THEN 'DC'
WHEN 'Employee - Products' THEN 'DC'
WHEN 'Past Client - Advanced'THEN 'GDC'
WHEN 'Student President' THEN 'ST'
WHEN 'Mentor' THEN 'DC'
WHEN 'MLHC in Process' THEN 'ST'
WHEN 'Employee - MLHC' THEN 'DC'
WHEN 'Guest CA' THEN 'GCA'
WHEN 'Associate of ML Doc' THEN 'AD'
WHEN 'Spouse' THEN 'SP'
WHEN 'CampII' THEN 'DC'
WHEN 'WAC' THEN 'DC'
WHEN '90 Day Client' THEN 'DC'
WHEN 'Website owner' THEN 'DC'
WHEN 'ML Canada' THEN 'DC'
WHEN 'MLMO' THEN 'DC'
WHEN 'Past Client - Elite' THEN 'GDC'
WHEN 'Past Client - Maintenance'THEN 'GDC'
WHEN 'Past Client - Student' THEN 'ST'
WHEN 'Past Client - MLHC' THEN 'GDC'
WHEN 'Leader' THEN 'DC'
WHEN 'Student Club Member' THEN 'GST'
WHEN 'Total Food Makeover' THEN 'DC'
WHEN 'Web Client' THEN 'DC'
WHEN 'Autism Whisperer' THEN 'DC'
WHEN 'Chiropractic Assistant' THEN 'CA'
WHEN 'MLHC Client' THEN 'DC'
WHEN 'Captain' THEN 'DC'
WHEN 'Retreat MLHC' THEN 'DC'
WHEN 'Guest Spouse' THEN 'GSP'
END) as Category
from [3rdi_EventParticipants] as EP
inner join [3rdi_EventSignup] as ES on EP.SignUpId = ES.SignUpId
WHERE EP.EventId = @ItemId AND EP.PlaceStatus IN (0,3,4,8)
and userid in(
select distinct userid from userroles
where roleid not in(19,20,21,22) and roleid not in(1,2, 25, 44))

[edit]
主题.
标签.
取消选中忽略HTML ..."以使块起作用.
将代码块移动到仅格式化的内容，而不是整个消息.
OriginalGriff [/edit]

[edit]
Subject.
Tags.
Unchecked "Ignore HTML..." to make blocks work.
Moved code blocks to only the formatted stuff, rather than whole message.
OriginalGriff[/edit]

推荐答案

我不太确定我是否了解您的问题，但这听起来像是对我来说很重要.选中此 [

I am not too sure if I have understood your question, but it does sounds like pivot/unpivot to me. Check this[^] if it helps.

就像丹麦人一样，我有点困惑.

希望这些对您有所帮助.

将行转换为列的五种方法 [将多列拆分为多行 [

Like danish I was confused too little bit.

I hope these will help you surely.

Five methods converting rows to columns[^]

Split Multiple Columns into Multiple Rows[^]

这篇关于当前表数据已轮换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！