Solved

sql server db select query

Posted on 2013-05-30
11
404 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 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