Solved

sql server db select query

Posted on 2013-05-30
11
393 Views
Last Modified: 2013-06-03
Hi

I how to fetch the data in sql server database like below

Ex:
I have a table t1 with two columns item and desc like below

item desc
---- ----
1    red
1    small
1    think
2    white
2    large


I need to fetch the data as below

item desc1 desc2 desc3 desc4
1    red   small think  null
2    white large null  null

I am using sql server database.

Please provide both the querys for sql server databse 2005 and 2008 versions if queries need to write differently in these versions.

Thanks.
0
Comment
Question by:GouthamAnand
  • 6
  • 4
11 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39207266
does this need to be dynamic? i.e. will the filtering change?
0
 
LVL 4

Expert Comment

by:Ahmed Hussein
ID: 39207277
SELECT item ,GROUP_CONCAT(type_count) FROM
(SELECT item,desc,CONCAT(desc,",",COUNT(*)) as type_count
FROM TABLE_NAME GROUP BY item,desc) as x  
GROUP BY item;
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39207278
see this working at: http://sqlfiddle.com/#!3/f26c7/1 
DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[desc]) 
            FROM table1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT item, ' + @cols + ' from 
            (
                select item, [desc]
                from table1
           ) x
            pivot 
            (
                 max([desc])
                for [desc] in (' + @cols + ')
            ) p '

--select @query
execute(@query)

Open in new window

it uses the PIVOT command
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39207281
if you have trouble using this in sql 2005 I have another approach that will work in both using case expressions and group by
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39207286
think group_concat is for MySQL (only)
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:GouthamAnand
ID: 39207310
Thank you for your responses.
But can you please suggest the case of static.   Means we assume that thre will be not more than 4 descriptions. ie 4 or less values only for any item.

Thanks.
0
 

Author Comment

by:GouthamAnand
ID: 39207323
Also, request you to provide a select query only as I need to join other tables and columns also for my original query. This requirement is part of my actual required query.
0
 

Author Comment

by:GouthamAnand
ID: 39207327
Group_concat is not working in sql server database.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39207331
just re-looked at your expected results, this one produces the following:
ITEM   DESC1  DESC2    DESC3
1      red    small    think
2      large  white    (null)

see: http://sqlfiddle.com/#!3/2cd6f/8 
DECLARE @query  AS NVARCHAR(MAX)

;WITH
COLS AS (
          SELECT
              ', max(CASE WHEN row_ref = '
            + convert(varchar,number)
            + ' THEN [desc] ELSE null END) AS Desc'
            + convert(varchar,number)
            AS casea
          FROM master.dbo.spt_values
          WHERE  TYPE = 'P'
          AND number BETWEEN 1 AND (
                                    SELECT max(c)
                                    FROM (
                                          SELECT
                                                item
                                              , count(*) AS c
                                          FROM table1
                                          WHERE 1=1  /* i.e. filter the data here */
                                          GROUP BY
                                                item
                                        ) AS x
                                    )
           )


select @query = 

  'WITH CTE as ('
+ '        select'
+ '            table1.item, table1.[desc]'
+ '          , row_number() over (partition by table1.Item order by table1.[desc]) as row_ref'
+ '        from table1'
+ '        where 1=1 /* i.e. filter the data here */'
+ '       )'
+ 'select Item, ' 
+ STUFF( 
           ( SELECT casea
             FROM cols
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')  
+ ' from CTE group by Item'

--select @query -- uncomment to inspect what the variable contains
exec(@query)
;

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39215252
>>But can you please suggest the case of static.  
apologies, I missed this request. The following is static and assumes at most 4 columns
WITH CTE
AS (
	SELECT
		  table1.item
		-- , table2.whatever -- add fields as needed
		, table1.[desc]
		, row_number() OVER (
			PARTITION BY table1.Item ORDER BY table1.[desc]
			) AS row_ref
	FROM table1
	-- INNER JOIN table2 on table1.id = table2.fk
	-- WHERE 1 = 1 /* i.e. if you need to filter the data, do it here */
	)
SELECT
	  Item
        --, whatever
	, max(CASE WHEN row_ref = 1 THEN [desc] ELSE NULL END) AS Desc1
	, max(CASE WHEN row_ref = 2 THEN [desc] ELSE NULL END) AS Desc2
	, max(CASE WHEN row_ref = 3 THEN [desc] ELSE NULL END) AS Desc3
	, max(CASE WHEN row_ref = 4 THEN [desc] ELSE NULL END) AS Desc4
FROM CTE
GROUP BY
   Item
--, whatever

Open in new window

http://sqlfiddle.com/#!3/2cd6f/10

If you are adding further tables and fields, you can gather the base data within the CTE just like a normal select. Once you have that done then you also need to amend the selection list and group by list of the lower query.

If you are having issues with this please let us know.

Also, just another thing, in your sample data you use the field name [desc] "DESC" is used in SQL to direct an order by so if the field name [desc] is really what you use please ensure it is surround by the square brackets.

and yes, group_concat() is MySQL only - it does not work in sql server. (I did mention this.)
0
 

Author Closing Comment

by:GouthamAnand
ID: 39215685
Thank you very much. I change the script accordingly to my requirement and  it works perfectly for my requirement.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now