select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

select query

Author

Topic

MichelleMA
Starting Member

32 Posts

Posted - 2010-03-22 : 12:07:43

Hi,

I have a table T with columns: A, B, C, D

Is it possible to return data in this form ?

loop dynamically on distinct values of A (a1, a2, a3)
and return

select a1, B where A=a1, C where A=a1, D where A=a1,
a2, B where A=a2, C where A=a2, D where A=a2,
a3, B where A=a3, C where A=a3, D where A=a3,

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-22 : 12:11:54

may be

SELECT A,B,C,D
FROM 
(
SELECT ROW_NUMBER() OVER (PARTITION BY A ORDER BY A) AS Seq,A,B,C,D
FROM Table
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MichelleMA
Starting Member

32 Posts

Posted - 2010-03-22 : 12:34:06

Thank you Visakh, I tried this, but it does not return the different values of one column as many columns. Referring to your example in the blog for OrderStock table, I would like to see something like this:

Item_Code OrderQty OrderNo Item_Code OrderQty OrderNo Item_Code ..
Item1 3000 mno Item2 1350 pqr Item3
Item1 2500 abc Item2 4300 dwq Item3

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-22 : 12:43:55

so you mean you want two row columns onto single row?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MichelleMA
Starting Member

32 Posts

Posted - 2010-03-22 : 12:58:32

Yes, if the values of Item_Code are : Item1, Item2, Item3. I am looking to see if we can return each value in a separate column doing some looping and joins maybe? and return these columns:

Item_Code_Item1Only, OrderQty_forItem1, Item_Code_Item2Only, OrderQty_forItem2, Item_Code_Item3Only, OrderQty_forItem3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-22 : 13:01:38

whats the need for this? bring all the rows into a single row?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MichelleMA
Starting Member

32 Posts

Posted - 2010-03-22 : 13:07:39

The user is asking for the data to be displayed in this format, not able to achieve this from the front end, and looking for possibility to return data this way as an alternative.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-22 : 13:15:35

so you will just have single row in o/p?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MichelleMA
Starting Member

32 Posts

Posted - 2010-03-22 : 13:25:56

no, I will have multiple rows, let's say we order the items from different suppliers, and in o/p we will have as many rows as companies we order the items from.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-22 : 13:27:44

oh ok...so one per company? but related info in different fields? or do you want them as csv?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MichelleMA
Starting Member

32 Posts

Posted - 2010-03-22 : 13:38:16

in different fields.

Thank you

MichelleMA
Starting Member

32 Posts

Posted - 2010-03-22 : 13:38:58

yes, one row per company

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources