Row transpose
-
Need help on t-sql query which turns rows into columns.
DML and DDL as follows:
Create Table #Test
(
[testId] [int] NULL,
[testVersion] [int] NULL,
[AdminType] [nvarchar](10) NULL,
[StatusId] [nvarchar](30) NULL,
[StatusDate] [datetime] NULL
)
Insert Into #Test ([testId],[testVersion], [AdminType],[StatusId], [StatusDate]) Values (1,2,'CAB','Accept','02/28/2011')
Insert Into #Test ([testId],[testVersion], [AdminType],[StatusId], [StatusDate]) Values (1,2,'RM','Accept','02/28/2011')
Insert Into #Test ([testId],[testVersion], [AdminType],[StatusId], [StatusDate]) Values (1,2,'Test','Accept','02/28/2011')
Insert Into #Test ([testId],[testVersion], [AdminType],[StatusId], [StatusDate]) Values (1,2,'Env','Accept','02/28/2011')
Insert Into #Test ([testId],[testVersion], [AdminType],[StatusId], [StatusDate]) Values (1,2,'TP','Accept','03/01/2011')
Select * From #Test Where testId = 1 and testVersion = 2
out put is
----------
testId testVersion AdminType StatusId StatusDate
1 2 CAB Accept 2011-02-28 00:00:00.000
1 2 RM Accept 2011-02-28 00:00:00.000
1 2 Test Accept 2011-02-28 00:00:00.000
1 2 Env Accept 2011-02-28 00:00:00.000
1 2 TP Accept 2011-03-01 00:00:00.000
Expected Format
testId testVersion CAB RM Test Env TP
1 2 Accept Accept Accept Accept Accept
Hope am clear on my requirement.
TIA...
-
You'll want to take a look at the Pivot and Unpivot operators in T-SQL.
Honestly though, I do everything I can to avoid doing that in the database or in T-SQL. It's just not that good at it. There are much better tools for pivoting data, like SSRS or Excel.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
I understand your suggestion regarding not to use in t-sql. But few things which are out of our control makes us to use these inside t-sql.
I appreciate if you can send me the query...
-
GSquared answered your question. 😀
As well as it seems you have the answer in your own signature under "Cross Tabs and Pivots" :hehe:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
SELECT testId,testVersion,
MAX(CASE WHEN AdminType='CAB' THEN StatusId END) AS CAB,
MAX(CASE WHEN AdminType='RM' THEN StatusId END) AS RM,
MAX(CASE WHEN AdminType='Test' THEN StatusId END) AS Test,
MAX(CASE WHEN AdminType='Env' THEN StatusId END) AS Env,
MAX(CASE WHEN AdminType='TP' THEN StatusId END) AS TP
FROM #Test
GROUP BY testId,testVersion;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark-101232 .. You rock...I was struggling with pivot..and you gave a solution by not using PIVOT..Thanks a lot mark....
-
NewBeeSQL (3/2/2011)
Mark-101232 .. You rock...I was struggling with pivot..and you gave a solution by not using PIVOT..Thanks a lot mark....As a suggestion, take the time to read the article about "Cross Tabs and Pivots" located in your signature line so you understand why the solution Mark posted works. 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply